I need to know if I can use the REPLACE function to remove bad characters from a data column. The data type is text and I would like to remove any comma's or quotes from the data. I assume that this would be the best way to remove these characters.

I've not been able to find much information on the REPLACE function so I'm not sure if the data can be replaced in the same column or if the
replacement data has to be placed in another column.

Any insight into this matter would be appreciated.

TechRick

update yourtable
set textcol = replace(replace(textcol,','),'''')

if there is no replacement character, the search character is removed

i'm pretty sure you can nest oracle functions (i can't test it because i don't gots no oracle database)

to create a string consisting of a single quote, i think you have to code two consecutive ones, hence the four of them in a row like that

rudy
http://rudy.ca/

Thanks for the reply.

I think there is a difference however in the SQL that I'm using verses yours. I'm running on SQL 2000 and doing my queries through the Query Analyzer.

The format for the function command you suggested doesn't seem to work properly for me.

In my SAM's Learn SQL in 21 Days book I have this format given:

SELECT LASTNAME, REPLACE(LASTNAME, 'ST') REPLACEMENT
FROM CHARACTERS;

OUTPUT:
LASTNAME REPLACEMENT
--------------- ---------------
CHRISTINE CHRIINE
ADAMS ADAMS
COSTALES COALES

----------------------------------------------------------

The problem I have with this is that it takes the replacement data and puts it into another column. I want to keep the updated data in the same column. I guess I may need to live with a 'replacement' column and disregard the original?

Thanks again for your help.

TechRick

dude, you posted in the "SQL and PL/SQL" forum, you should've posted in the "Microsoft SQL/Server" forum

i naturally assumed oracle, since REPLACE is an oracle function

in sql.server, the replacement string is not optional, so you have to specify it as a zero-length string

see http://msdn.microsoft.com/library/en...ra-rz_76lh.asp

so try

update yourtable
set textcol = replace(replace(textcol,',',''),'''','')

Sorry for the confusion. I didn't realize I was in the wrong place.

I'm a newbie to SQL and have only been using it for less than 2 weeks. I'm not yet familiar with all the differences in SQL formats.

I'll try to find my way to the correct forum in the future.

Thanks again for your help.

TechRick

hey, no problem, i just thought i'd mention why you got an oracle answer instead of an sql/server answer the first time (i am not the moderator of either of these two forums)

so, did the sql/server version work?

Rudy,

I just finished testing it and sure enough it did the trick! Thanks much for all the help.

One more question, why did you structure it this way:

replace(replace(textcol,',',''),'''','')


I thought it would need to be replace (texcol, ",", " ")

Why the extra replace and the added ),'''','')?

Just wondering.

Thanks again,
TechRick

one removes commas, the other removes quotes

nesting them passes the result of one into the other

beats using two update stements, eh


rudy

Very nice.

I should have guessed that was the case.

Thanks again for all your help.

Best Regards,
TechRick

I think we can also do this using TRANSLATE Function. I tested this one only in ORACLE and not in SQL-Server. I dont know whether we have one similar funtion in SQL-Server.

SELECT emp_id ,
emp_name,
TRANSLATE( emp_name, '%!@#$^', ' ' )
FROM employee_table ;

Regards,
Sreekon.