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.
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 ...