Replacing a single character

djlerman · #1 (**permalink**) 06-04-04, 17:26

I have fields that have a bunch of characters I want to replace. How would I do that with an SQL statement. I could do this PERL but I wanted to hold off to see if I could do it directly in SQL first.

The Characters are:
CHR(12) with space
^ with '
CHR(145) with '
CHR(146) with '
etc.....

Pat Phelan · #2 (**permalink**) 06-04-04, 17:29

Quote:

Originally Posted by djlerman

I have fields that have a bunch of characters I want to replace. How would I do that with an SQL statement. I could do this PERL but I wanted to hold off to see if I could do it directly in SQL first.

The Characters are:
CHR(12) with space
^ with '
CHR(145) with '
CHR(146) with '
etc.....

Use Perl! A simple tr/// is much easier than the shenanigans you'll have to perform to simulate it using SQL!

-PatP

Leaden · #3 (**permalink**) 06-08-04, 15:55

You declared "shenanigans" eheheh

djlerman · #4 (**permalink**) 06-15-04, 12:20

O.K I figured it out (with the help of blogbarter.com). For any others out there that may need it. Here is the SQL statement to replace a single character:

Code:

update tablename set field = replace(field,'search','replace');

or for ASCII Code:

Code:

update tablename set field = replace(field,char(012),char(032));

Here is the link:
http://analogduck.com/blog/modules/n...hp?storyid=179

Internet Services

Web Development

Digital Marketing

Consumer Tech