String substitution?

[jenwinkelman]

If I want to convert a white space to an underscore how do I do it?

Ex. I have a table with a field NAME. The NAME field always has the format 'ABC x' (where x is a number 0 -255) and I want to convert them all to 'ABC_x'. How do I do it? I need to loop through the table and convert the NAME field in all the rows.

 

[carp]

This probably depends on your RDBMS.
In Oracle, you can try
UPDATE my_table
SET name = replace(name,' ','_');

 

[jenwinkelman]

Thanks! It worked like a charm.
Guess I should have specified that I am using Oracle. I was hoping that it would be as easy as this and I didn't have to write some nasty little function using SUBSTR and concatination.

 

[tlbroadbent]

For future reference - this forum is about ANSI SQL. Oracle has a level of compliance to the ANSI standard but it also has many extensions. You should post Oracle questions in an Oracle forum in order to get the best answers. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.