Replace a newline in TSQL

Question

I would like to replace (or remove) a newline character in a TSQL string. Any ideas?

The obvious

REPLACE(@string, CHAR(13), '')

just won't do it...

Answer 1

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

Answer 2

REPLACE(@string, CHAR(13) + CHAR(10), '')

Answer 3

I may be a year late to the party, but I work on queries & MS-SQL every day, and I got tired of the built-in functions LTRIM() & RTRIM() (and always having to call them together), and of not catching 'dirty' data that had newlines at the end, so I decided it was high time to implement a better TRIM function. I'd welcome peer feedback!

Disclaimer: this actually removes (replaces with a single whitespace) extended forms of whitespace (tab, line-feed, carriage-return, etc.), so it's been renamed as "CleanAndTrim" from my original answer. The idea here is that your string doesn't need such extra special-whitespace characters inside it, and so if they don't occur at the head/tail, they should be replaced with a plain space. If you purposefully stored such characters in your string (say, your column of data that you're about to run this on), DON'T DO IT! Improve this function or write your own that literally just removes those characters from the endpoints of the string, not from the 'body'.

Okay, now that the disclaimer is updated, here's the code.

-- =============================================
-- Description: TRIMs a string 'for real' - removes standard whitespace from ends,
-- and replaces ASCII-char's 9-13, which are tab, line-feed, vert tab,
-- form-feed, & carriage-return (respectively), with a whitespace
-- (and then trims that off if it's still at the beginning or end, of course).
-- =============================================
CREATE FUNCTION [fn_CleanAndTrim] (
       @Str nvarchar(max)
)
RETURNS nvarchar(max) AS
BEGIN
       DECLARE @Result nvarchar(max)

       SET @Result = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
              LTRIM(RTRIM(@Str)), CHAR(9), ' '), CHAR(10), ' '), CHAR(11), ' '), CHAR(12), ' '), CHAR(13), ' ')))

       RETURN @Result
END

Cheers!

Another Disclaimer: Your typical Windows line-break is CR+LF, so if your string contains those, you'd end up replacing them with "double" spaces.

UPDATE, 2016: A new version that gives you the option to replace those special-whitespace characters with other characters of your choice! This also includes commentary and the work-around for the Windows CR+LF pairing, i.e. replaces that specific char-pair with a single substitution.

IF OBJECT_ID('dbo.fn_CleanAndTrim') IS NULL
    EXEC ('CREATE FUNCTION dbo.fn_CleanAndTrim () RETURNS INT AS BEGIN RETURN 0 END')
GO
-- =============================================
-- Author: Nate Johnson
-- Source: http://stackoverflow.com/posts/24068265
-- Description: TRIMs a string 'for real' - removes standard whitespace from ends,
-- and replaces ASCII-char's 9-13, which are tab, line-feed, vert tab, form-feed,
-- & carriage-return (respectively), with a whitespace or specified character(s).
-- Option "@PurgeReplaceCharsAtEnds" determines whether or not to remove extra head/tail
-- replacement-chars from the string after doing the initial replacements.
-- This is only truly useful if you're replacing the special-chars with something
-- **OTHER** than a space, because plain LTRIM/RTRIM will have already removed those.
-- =============================================
ALTER FUNCTION dbo.[fn_CleanAndTrim] (
    @Str NVARCHAR(MAX)
    , @ReplaceTabWith NVARCHAR(5) = ' '
    , @ReplaceNewlineWith NVARCHAR(5) = ' '
    , @PurgeReplaceCharsAtEnds BIT = 1
)
RETURNS NVARCHAR(MAX) AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)

    --The main work (trim & initial replacements)
    SET @Result = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        LTRIM(RTRIM(@Str))  --Basic trim
        , NCHAR(9), @ReplaceTabWith), NCHAR(11), @ReplaceTabWith)   --Replace tab & vertical-tab
        , (NCHAR(13) + NCHAR(10)), @ReplaceNewlineWith) --Replace "Windows" linebreak (CR+LF)
        , NCHAR(10), @ReplaceNewlineWith), NCHAR(12), @ReplaceNewlineWith), NCHAR(13), @ReplaceNewlineWith)))   --Replace other newlines

    --If asked to trim replacement-char's from the ends & they're not both whitespaces
    IF (@PurgeReplaceCharsAtEnds = 1 AND NOT (@ReplaceTabWith = N' ' AND @ReplaceNewlineWith = N' '))
    BEGIN
        --Purge from head of string (beginning)
        WHILE (LEFT(@Result, DATALENGTH(@ReplaceTabWith)/2) = @ReplaceTabWith)
            SET @Result = SUBSTRING(@Result, DATALENGTH(@ReplaceTabWith)/2 + 1, DATALENGTH(@Result)/2)

        WHILE (LEFT(@Result, DATALENGTH(@ReplaceNewlineWith)/2) = @ReplaceNewlineWith)
            SET @Result = SUBSTRING(@Result, DATALENGTH(@ReplaceNewlineWith)/2 + 1, DATALENGTH(@Result)/2)

        --Purge from tail of string (end)
        WHILE (RIGHT(@Result, DATALENGTH(@ReplaceTabWith)/2) = @ReplaceTabWith)
            SET @Result = SUBSTRING(@Result, 1, DATALENGTH(@Result)/2 - DATALENGTH(@ReplaceTabWith)/2)

        WHILE (RIGHT(@Result, DATALENGTH(@ReplaceNewlineWith)/2) = @ReplaceNewlineWith)
            SET @Result = SUBSTRING(@Result, 1, DATALENGTH(@Result)/2 - DATALENGTH(@ReplaceNewlineWith)/2)
    END

    RETURN @Result
END
GO

Answer 4

The Newline in T-SQL is represented by CHAR(13) & CHAR(10) (Carriage return + Line Feed). Accordingly, you can create a REPLACE statement with the text you want to replace the newline with.

REPLACE(MyField, CHAR(13) + CHAR(10), 'something else')

Answer 5

To do what most people would want, create a placeholder that isn't an actual line breaking character. Then you can actually combine the approaches for:

REPLACE(REPLACE(REPLACE(MyField, CHAR(13) + CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(10), 'something else')

This way you replace only once. The approach of:

REPLACE(REPLACE(MyField, CHAR(13), ''), CHAR(10), '')

Works great if you just want to get rid of the CRLF characters, but if you want a placeholder, such as

<br/>

or something, then the first approach is a little more accurate.

Answer 6

In SQL Server 2017 & later, use Trim

Select Trim(char(10) + char(13) from @str)

1. it trims on starting and ending, not in the middle
2. the order of \r and \n does not matter

I use it to trim special characters for a file name

Select Trim(char(10) + char(13) + ' *<>' from @fileName)

Answer 7

If your column data type is 'text' then you will get an error message as

Msg 8116, Level 16, State 1, Line 2 Argument data type text is invalid for argument 1 of replace function.

In this case you need to cast the text as nvarchar and then replace

SELECT REPLACE(REPLACE(cast(@str as nvarchar(max)), CHAR(13), ''), CHAR(10), '')

Answer 8

Sometimes

REPLACE(myString, CHAR(13) + CHAR(10), ' ')

won't work. In that case use the following snippet code:

REPLACE(REPLACE(myString, CHAR(13),''), CHAR(10), ' ')

Answer 9

I was wanting to sanitize the contents of a column to generate a csv file, so want to get rid of the comma (,) within the varchar as well as newline and carrage-return.

I also wanted to eventually use the generated csv to create another script (to insert rows into another db) so also needed to change ' within the varchar to '' so ended up with this...

REPLACE(REPLACE(REPLACE(REPLACE(ErrorMessage, CHAR(13), ''), CHAR(10), ''),',',''),'''','''''')

There may be other nicer ways but it got the job done.

Answer 10

If you have an issue where you only want to remove trailing characters, you can try this:

WHILE EXISTS
(SELECT * FROM @ReportSet WHERE
    ASCII(right(addr_3,1)) = 10
    OR ASCII(right(addr_3,1)) = 13
    OR ASCII(right(addr_3,1)) = 32)
BEGIN
    UPDATE @ReportSet
    SET addr_3 = LEFT(addr_3,LEN(addr_3)-1)
    WHERE 
    ASCII(right(addr_3,1)) = 10
    OR ASCII(right(addr_3,1)) = 13
    OR ASCII(right(addr_3,1)) = 32
END

This solved a problem I had with addresses where a procedure created a field with a fixed number of lines, even if those lines were empty. To save space in my SSRS report, I cut them down.

Answer 11

If you have have open procedure with using sp_helptext then just copy all text in new sql query and press ctrl+h button use regular expression to replace and put ^\n in find field replace with blank . for more detail check image.enter image description here

Answer 12

To @Cerebrus solution: for H2 for strings "+" is not supported. So:

REPLACE(string, CHAR(13) || CHAR(10), 'replacementString')

Answer 13

The answer posted above/earlier that was reported to replace CHAR(13)CHAR(10) carriage return:

REPLACE(REPLACE(REPLACE(MyField, CHAR(13) + CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(10), 'something else')

Will never get to the REPLACE(MyField, CHAR(13) + CHAR(10), 'something else') portion of the code and will return the unwanted result of:

'something else''something else'

And NOT the desired result of a single:

'something else'

That would require the REPLACE script to be rewritten as such:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(13) + CHAR(10), 'something else')

As the flow first tests the 1st/Furthest Left REPLACE statement, then upon failure will continue to test the next REPLACE statement.

Answer 14

A slightly less technical answer but rooted in the same issue. If anyone is outputting reports to txt files and wondering why their outputs are breaking into new lines even though your query specifically has replacements on new lines and you ae removing every char10 char 9 char13 you can find but the txt output still rolling into new lines. Do yourself a favor and just check the editor youre opening the text file with isn't wrapping newlines due to the length of the record being >1024 characters in length. If you output the same result to csv for example you can confirm if a line break is actually happening by seeing if the line break is still present or not. if not present then likely the issue is the notepad line length limiter which was my issue. issue disappeared once I moved to CSV output. If you need it to stay in txt then maybe consider cutting down the length of your query on certain fields

Answer 15

I don't think anyone covered this sligtly counter intuitive way

REPLACE(MyField, '
', '')

Where you just pres return inside constant.