[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Replace question

vncntj

3/28/2007 3:55:00 PM

How can I use the Replace function for strings like "St"

Replace(Addr, "St", "Street")


15 East St

updated to:

15 East Street

instead of

15 EaStreet Street.

Thanks,
Vincent
5 Answers

Jack Vamvas

3/28/2007 4:02:00 PM

0

Replace(Addr, " St", " Street") --note : space


--

Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITj...



<vncntj@hotmail.com> wrote in message
news:1175097282.420738.272790@r56g2000hsd.googlegroups.com...
> How can I use the Replace function for strings like "St"
>
> Replace(Addr, "St", "Street")
>
>
> 15 East St
>
> updated to:
>
> 15 East Street
>
> instead of
>
> 15 EaStreet Street.
>
> Thanks,
> Vincent
>


Aaron [SQL Server MVP]

3/28/2007 4:08:00 PM

0

Well, you could replace ' St' (leading space), but what about

155 Stockwell St

? You could look for 'St' at the *end* of the data, but what about

155 East St W

? What about when St actually meant station and not street?

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...



<vncntj@hotmail.com> wrote in message
news:1175097282.420738.272790@r56g2000hsd.googlegroups.com...
> How can I use the Replace function for strings like "St"
>
> Replace(Addr, "St", "Street")
>
>
> 15 East St
>
> updated to:
>
> 15 East Street
>
> instead of
>
> 15 EaStreet Street.
>
> Thanks,
> Vincent
>


Raymond D'Anjou

3/28/2007 5:03:00 PM

0

If you know that there will always be a space before 'St' then:
Replace(Addr, ' St', ' Street')

Of course, you'll have a problem with:
456 St. Patrick's St
64 Stern St

If 'St' is always at the end, maybe your best bet would be to integrate a
CASE:
CASE RIGHT(Addr, 2) WHEN 'St' ...

Even here, you'll have to handle street names that end with 'St'.

<vncntj@hotmail.com> wrote in message
news:1175097282.420738.272790@r56g2000hsd.googlegroups.com...
> How can I use the Replace function for strings like "St"
>
> Replace(Addr, "St", "Street")
>
>
> 15 East St
>
> updated to:
>
> 15 East Street
>
> instead of
>
> 15 EaStreet Street.
>
> Thanks,
> Vincent
>


Kamran

3/28/2007 8:31:00 PM

0

Hi,

You can use:

LTrim(RTrim(Replace(' ' + Addr + ' ', ' St ', ' Street '))) --added a space
at begining and at end, replaced and trimmed spaces

HTH

Kamran

<vncntj@hotmail.com> wrote in message
news:1175097282.420738.272790@r56g2000hsd.googlegroups.com...
> How can I use the Replace function for strings like "St"
>
> Replace(Addr, "St", "Street")
>
>
> 15 East St
>
> updated to:
>
> 15 East Street
>
> instead of
>
> 15 EaStreet Street.
>
> Thanks,
> Vincent
>


Aaron [SQL Server MVP]

3/28/2007 8:40:00 PM

0

I still don't think it's safe to assume that any independent "st" is really
"Street":


DECLARE @Addr VARCHAR(32);
SET @Addr = '123 St Elmo''s Fire St'
SELECT LTrim(RTrim(Replace(' ' + @Addr + ' ', ' St ', ' Street ')))


Demographic data cleanup is not so simple, and in a lot of cases it's just
easier and safer to give out what they put in. If that's garbage in,
garbage out, so be it. Maybe a better place to do this is to have
validation on the client. "I see you entered St, did you mean Street?"

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...




"Kamran" <ahmadkamran@gmail.com> wrote in message
news:e%237P6fXcHHA.648@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> You can use:
>
> LTrim(RTrim(Replace(' ' + Addr + ' ', ' St ', ' Street '))) --added a
> space at begining and at end, replaced and trimmed spaces
>
> HTH
>
> Kamran
>
> <vncntj@hotmail.com> wrote in message
> news:1175097282.420738.272790@r56g2000hsd.googlegroups.com...
>> How can I use the Replace function for strings like "St"
>>
>> Replace(Addr, "St", "Street")
>>
>>
>> 15 East St
>>
>> updated to:
>>
>> 15 East Street
>>
>> instead of
>>
>> 15 EaStreet Street.
>>
>> Thanks,
>> Vincent
>>
>
>