Help with a replace function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a replace function

Author

Topic

V.V.
Starting Member

32 Posts

Posted - 2011-10-28 : 04:44:56

Guys,

I need to remove from an address the postal code and I got stock on something.

So, I have this code:

SELECT...,
replace(replace(replace(replace(client_address2,'Bloc','Bl.'),'Scara','Sc.'),'Etaj','Et.'),', Cod postal','') as address2
from V1.dbo.Address

Ok, everything works just fine until "Cod postal". In the address, "Cod postal" is like this "Cod postal xxxxxx" where x in (0-9). With replace function I can remove only "Cod postal". How can I remove the numeric part from the string also?

I tried with "Cod postal%" but it doesn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 04:57:30

why are you using series of replaces? this code is not scalable as any change in format means you've to write a new set of code with replace functions. why not use patindex or charindex to shred string based on a delimiter if its consistent. if you can show some sample values, we will try to come up with a more generic and scalable solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-28 : 04:57:56

Have a look at STUFF()

No, you're never too old to Yak'n'Roll if you're too young to die.

V.V.
Starting Member

32 Posts

Posted - 2011-10-28 : 05:20:01

#£visakh16, I need to modify a procedure because it must export a .csv file. Ok, in this .csv file I have two columns:

- column1 named Address1 contains the name of the street + the number of the street. (Ex: Str. Mainstreet, Nr. 1)
- column2 named Address2 contains the block + the stair + the floor + the apartment + the postal code (Ex: Bl. A1, Sc. 1, Et. 1, Ap. 1, Cod postal 000001)

The field separator between the components of the address (Address1 and Address2) is ",". I need to replace comma with space " " (because this is the original format of Address1 and Address2). The easiest way to do that was by replacing "," with " " and to short "Bloc" with "Bl", "Scara" with "Sc"... and so on.

The issue is on "Cod postal" because I need to remove it from Address2 completely. That's why I got stock here.

The original: Str. Mainstreet, Nr. 1, Bloc A1, Scara 1, Etaj 1, Ap. 1, Cod postal 000001
How I want to be: Str. Mainstreet Nr. 1 Bl. A1, Sc. 1 Et. 1 Ap. 1

I hope I got myself clear now. I don't know how to explain other way. :) If you have a better solution, please let me know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 05:31:34

ok if thats the case why not creating a mapping table (AddressMapping) and use it for the replace to make it generic like

AddressWord Code
---------------- ----------------------
Bloc Bl
Scara Sc
Etaj Et

then use it like


UPDATE t
SET t.Address1=REPLACE(REPLACE(t.Address1,',',' '),' ' + m.AddressPart + ' ',m.Code),
t.Address2=REPLACE(REPLACE(t.Address2,',',' '),' ' + m.AddressPart + ' ',m.Code)
FROM yourtable t
INNER JOIN AddressMapping m
ON t.Address1 LIKE '% ' + m.AddressPart + ' %'
OR t.Address2 LIKE '% ' + m.AddressPart + ' %'

to make the changes and then use below to ignore numeric part

UPDATE t
SET t.Address2=LEFT(t.Address2,PATINDEX('%Codepostal%',t.Address2)-1)
FROM yourtable t
WHERE PATINDEX('%Codepostal%',t.Address2)>0

I hope postal always comes last

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

V.V.
Starting Member

32 Posts

Posted - 2011-10-28 : 05:53:55

#£visakh16, wow! Thank you very much for this.

The idea is that I need to modify today that procedure in the way I explain it. I don't have enough time to test you solution. :(

I thought I could resolve it somehow with replace function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 05:57:43

ok. try the method. it will save lots of unnecessary replace statements and also make it generic so whenver you want make a word convertion just add it to table with corresponding code and no need to tweak your replace code to handle it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 10:10:06

quote:
Originally posted by V.V.
I don't have enough time to test you solution. :(

Oh really? Good Luck

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

V.V.
Starting Member

32 Posts

Posted - 2011-10-31 : 10:01:13

#X002548, at that time I didn't have enough time to test #visakh16's solution. As you can see, I thank to him for it and I told him that I will test it after that.

Have a nice day. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 11:18:55

quote:
Originally posted by V.V.

#X002548, at that time I didn't have enough time to test #visakh16's solution. As you can see, I thank to him for it and I told him that I will test it after that.

Have a nice day. :)

thats ok
hope it gave you what you were looking after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources