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

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

Owen


Read Other SQL Guru Questions



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