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

User · Accepted Answer

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

User · Answer

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

User · Answer

The Newline in T-SQL is represented by CHAR 13   amp  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

User · Answer

REPLACE  string  CHAR 13    CHAR 10

User · Answer

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  lt br  gt   or something  then the first approach is a little more accurate

User · Answer

I may be a year late to the party  but I work on queries  amp  MS-SQL every day  and I got tired of the built-in functions LTRIM    amp  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   amp  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  --  amp  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  amp  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  amp  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  amp  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

User · Answer

In SQL Server 2017  amp  later  use Trim Select Trim char 10    char 13  from  str    it trims on starting and ending  not in the middle 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        lt  gt   from  fileName

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

To  Cerebrus solution  for H2 for strings     is not supported  So   REPLACE string  CHAR 13     CHAR 10    replacementString

[tsql] Replace a newline in TSQL

Examples related to tsql

Examples related to newline