Logging Experiences

T-SQL: remove or replace line breaks

Posted in Programming, SQL, Tips and Tricks by Sina Iravanian on September 6, 2012

Sometimes you might need to remove line breaks from a column value to make a one-line data. I prefer to replace line-breaks with a single space, rather than removing them. This is how to do it with Transact-SQL:

REPLACE(REPLACE(ISNULL( SomeTable.SomeColumn, ''), CHAR(13), ''), CHAR(10), ' ')

To remove line-breaks completely replace the final space string with an empty string.

Tagged with: ,

T-SQL: checking if a string is empty or white-space

Posted in Programming, SQL, Tips and Tricks by Sina Iravanian on September 6, 2012

Consider a case where you want to bring through a varchar column called SomeColumn from table SomeTable and replace its value with, 'Not Available' if the value is NULL or an empty string. This is how it can be achieved in Transact-SQL:

SELECT ISNULL(NULLIF(SomeTable.SomeColumn, ''), 'Not Available')
FROM SomeTable
...

The above code is checking for null or emptiness of the string. To check for being null or white-space, use the following code instead:

SELECT ISNULL(NULLIF(LTRIM(RTRIM(SomeTable.SomeColumn)), ''), 'Not Available')
FROM SomeTable
...
Tagged with: , ,