SQL to manipulate strings in column

[bigbalbossa]

I'm working with a column w/ Address infomation. For example:

310 West Street
19 Chandlers Boulevard
32 W Avenue

How can sql change Street to ST, Boulevard to BLVD, and Avenue to AVE, so my output will look like:

310 West ST
19 Chandlers BLVD
32 W AVE

Regards,

 

[LKBrwnDBA]

Depending on you DBMS, check the "REPLACE" function.

In other words, read the manual.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[juliane26]

It is not that easy, since REPLACE would allow only one string to be replaced and not three.

Still, of course it can be done using case as well:

select 
   case 
   when c1 like '%Street' then replace(c1,'Street','STR')
   when c1 like '%Boulevard' then replace(c1,'Boulevard','BVD')
   when c1 like '%Avenue' then replace(c1,'Avenue','AVE')
   else c1
   end
from t1

Anyway, better be careful: string operations tend to be very expensive, indexes cannot be used on expressions like '%xyz'.

Juliane

 

[bigbalbossa]

Thank you Julian. I was looking into using replace w/in a case statement, but like you said...it's expensive. My tables not too big at 4MM records. Guess i'll just let it run.

Thanks for the help!

 

[Golom]

It is not that easy ...

Sorta ... but you can use multiple invocations

Replace ( Replace ( Replace ( c1, "Street", "ST" ), "Boulevard", "BLVD"), "Avenue", "AVE")

You are correct however ... string operations are expensive.

 

[juliane26]

nice golom, didn't think of that, I would like to see the explain of both

Juliane