The SQL Guru Answers your Questions...
|
In MS SQL Server how can I do a character replace? Example: In a
specific field, how do I replace all spaces with an underscore
character, in every record containing a space?
More information:
I needed to replace all spaces in a name field with underscore
characters, so a different program would recognize the entire field as a
single entry. I couldn't think of a quick way so I had to replace about
400 spaces by hand. Not very fun.
|
T-SQL has a very useful function called REPLACE. What this does is look for
the occurances of a specified string in your target and replace it with
another one. So we can use this format:
<result> = replace(<source>, ' ', '_')
|
More specific to your case, you need to
UPDATE <table>
SET name = REPLACE(LTRIM(RTRIM(name)), ' ', '_')
|
Note that I've added the TRIMs to ensure there's no leading and trailing
spaces, as I don't suppose you would need to convert those to underscores.
Good Luck!
Owen
