Problems with SQL Replace - 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

Problems with SQL Replace

Author

Topic

DanielF
Starting Member

2 Posts

Posted - 2007-09-17 : 06:22:54

Hello Members !
my problem is as follow:i have a SQL 2005 database for using Microsoft CRM 3.0. Now i have to make a change in one data field that all of the 5,000 telephonenumbers have the same consistent layout as follow:

national numbers: 05361-920149
International numbers: 0033-58675-9699
Mobil numbers: 0172-8054768

At the moment the numbers in the database are inconsistent:

national numbers:
ABC 05361-320149
DEF 05361-320-161
GHI 05361/920-144
JKL 05361 - 920111

International numbers:
MNO 00335-46759630
PQR 00335-4675-9630

Mobil numbers:
STU 0172/8054768

I asked somey else and i got following answer:

UPDATE phoneumbers
SET telefon = Replace(Replace(Replace(Replace(Replace([Telefon],"/","-")," ",""),"-","#",1,1),"-",""),"#","-")
WHERE (((Telefon)<>""));

It works, but only in Access. The problem ist the syntax of 'REPLACE'. In Access (TSQL) you can say: replace("-","#",1,1) but not in SQL.

Do you know what i mean ?

Greetings Daniel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 07:03:47

You can make use of STUFF function. Read about it in sql server help file

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 07:44:07

Replace works in SQL too! But you have to replace

the double quotes with single quotes.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 08:35:38

quote:
Originally posted by Peso

Replace works in SQL too! But you have to replace the double quotes with single quotes.

E 12°55'05.25"
N 56°04'39.16"

Well. OP has problem with this bolded part


UPDATE phoneumbers 
SET telefon = Replace(Replace(Replace(Replace(Replace([Telefon],"/","-")," ",""),"-","#",1,1),"-",""),"#","-")
WHERE (((Telefon)<>""));

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 09:31:20

[code]declare @sample table (data varchar(50))

insert @sample
select 'ABC 05361-320149' union all
select 'DEF 05361-320-161' union all
select 'GHI 05361/920-144' union all
select 'JKL 05361 - 920111' union all
select 'MNO 00335-46759630' union all
select 'PQR 00335-4675-9630' union all
select 'STU 0172/8054768'

-- this?
select stuff(replace(replace(replace(stuff(data, pos, 1, '@'), ' ', ''), '-', ''), '@', '-'), spc, 0, ' ')
from (
select data,
charindex(' ', data) as spc,
4 + patindex('%[^0-9]%', substring(data, 5, 8000)) as pos
from @sample
) as d[/code]

E 12°55'05.25"
N 56°04'39.16"

DanielF
Starting Member

2 Posts

Posted - 2007-09-17 : 09:36:53

Hello Madhivanan, hello Peso

i found the answer, here it is in 2 steps:

(1) UPDATE phoneumbers
SET telefon = REPLACE ( REPLACE ( telefon , '/', '-' ), ' ', '' )

(2) UPDATE phoneumbers
SET telefon = LEFT ( telefon, charindex ( '-', telefon )) + REPLACE ( RIGHT ( telefon, len ( telefon ) - charindex ( '-', telefon )), '-', '') FROM phonenumbers

Thanks for your help and Greetings from Stuttgart !

Daniel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 09:43:17

[code]declare @sample table (data varchar(50))

insert @sample
select 'ABC 05361-320149' union all
select 'DEF 05361-320-161' union all
select 'GHI 05361/920-144' union all
select 'JKL 05361 - 920111' union all
select 'MNO 00335-46759630' union all
select 'PQR 00335-4675-9630' union all
select 'STU 0172/8054768'

SELECT theOperator + ' ' + REPLACE(REPLACE(STUFF(theNumber, CHARINDEX('-', theNumber), 1, '@'), '-', ''), '@', '-') AS theNewData
FROM (
SELECT LEFT(Data, theSpace - 1) AS theOperator,
REPLACE(REPLACE(SUBSTRING(Data, thePosition, 8000), ' ', '-'), '/', '-') AS theNumber
FROM (
SELECT Data,
CHARINDEX(' ', Data) AS theSpace,
PATINDEX('%[0-9]%', Data) AS thePosition
FROM @Sample
) AS d
) AS e[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources