I'm not quite as proficient with SQL as I'd like, so when I stumble across little SQL gems I know I'll use again I like to note them down; what better a place to do that than my blog to share with the rest of the web :)
This is a Transact SQL example, on how to replace a character on a select. The character being replaced is just the ASCII code of the character.
SELECT REPLACE($FIELD, CHAR($ASCII_CODE_OF_CHAR_TO_REPLACE), '$CHAR_TO_REPLACE_WITH') FROM $TABLE
I find this very useful when replacing line breaks with a space on a selected field:
SELECT REPLACE(MyField, CHAR(10), ' ') FROM MyTable
Other frequently used characters you may want to replace:
Tab -> char(9)
Line feed -> char(10)
Carriage return -> char(13)
2 comments:
This helped me out in a bind. It's been a couple years since I've touched SQL. Using your solution, I was able to fix some problems with a customers site.
I did end up having to replace both char(10) and char(13) to compeletely eliminate my problems. Thanks for the simple and direct post.
Hi Neil.. Thanks a lot for shring this. This came as a very quick help just when I needed it most.. reminded me of the saying "a friend in need is friend indeed".. Thanks for posting this useful post in a very simple manner.
Post a Comment