Replace function: what instead?

[davideart]

Hy, I need your help about this problem: I have a table with 20.000 records (more or less) and I need to substitute a certain character in this table's fields with another (e.g. substitute "@" with "a&quot.I've found the REPLACE function but it seems to work only with SQL Server SP2. Do you know of any other Stored Procedure that can do the work? It's very important for me. Thank you for your help.

 

[foxbox]

What is your SQL version?
REPLACE works in my version MS-SQL 7 SP3

br
Gerard

 

[davideart]

Hy, foxbox, you are right, I forgot to specify that that the SQL version on which REPLACE doesn't run is the 6.5. Hope to hear good news.Bye

 

[JH123]

I was just going to post this exact same problem. I'll wait with you for the good news.

 

[JH123]

I figured out a way to do it using STUFF and CHARINDEX
stuff(column name, charindex('@', column name),1, 'a') THis will replace your @'s with a's but if you have a column without an @ it gives you a null. I think you may have to use a case to solve that problem, I'm working on that now, but I hope this helps you.

 

[davideart]

I worked around the problem using the followin' script, which uses Charindex and update. I don't know if it's the best solution but it works.
UPDATE tablename
SET fieldname = substring(fieldname,1,CHARINDEX('@',fieldname)-1)+substring(fieldname,CHARINDEX('@',fieldname)+1,DATALENGTH(fieldname)-CHARINDEX('@',fieldname))
WHERE CHARINDEX('@',fieldname) > 0
WHILE (@@rowcount > 0)
BEGIN
UPDATE tablename
SET fieldname = substring(fieldname,1,CHARINDEX('@',fieldname)-1)+substring(fieldname,CHARINDEX('@',fieldname)+1,DATALENGTH(fieldname)-CHARINDEX('@',fieldname))
WHERE CHARINDEX('@',fieldname) > 0
END