When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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!


Read Other SQL Guru Questions

ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article