Saturday, 15 March 2008

SQL - Replace line breaks and other characters

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:

Convergence Training said...

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.

Satish Kumar said...

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.