13

Facing a problem, it seems my data stored in SQL Server does not stored correctly, simply put, how to verify that a varchar data has carriage return and line feed in it? I try to print them out, does not show the special characters. Thanks

NateJ
  • 1,935
  • 1
  • 25
  • 34
TOMMY WANG
  • 1,382
  • 3
  • 16
  • 39

3 Answers3

20

You can use SQL Server's char(n) and contains() functions to match on field contents in your WHERE clause.

carriage return: char(13)
line feed:       char(10)

The following SQL will find all rows in some_table where the values of some_field contain newline and/or carriage return characters:

SELECT * FROM some_table 
WHERE CONTAINS(some_field, char(13)) OR CONTAINS(some_field, char(10))

To remove carriage returns in your some_field values you could use the replace() function long with char() and contains(). The SQL would look something like:

UPDATE some_table 
SET some_field = REPLACE(some_field, char(13), '')
WHERE CONTAINS(some_field, char(13))

To remove new lines you can follow up the last statement with the char(10) version of that update. How you do it all depends on what types of newlines your text contains. But, depending on where the text was inserted/pasted from the new lines may be \r\n or \n so running updates against both \r and \n characters would be safer than assuming that you're getting one version of newline or the other.

Note that if the newlines were removed and you want to retain them then you have to fix the issue at the point of entry. You can't replace or fix what has been removed so you should save the original text data in a new column that holds the original, unmodified text.

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • 5
    `SSMS` gives me an error when trying to run your `UPDATE` script `Incorrect syntax near 'char'.` with the final `char(13)` underlined with wiggly lines. Hovering over the wigglies it says `Incorrect syntax near 'char'. Expecting STRING, TEXT_LEX, or VARIABLE` – whytheq Oct 01 '14 at 16:16
  • @whytheq: You're asking a question in the comments section. I understand it's related to/based on my answer but it's hard to guess what's going on w/o seeing the actually query you're using and the table structure you're running against. Please post a question. Thanks. – Paul Sasik Oct 01 '14 at 18:01
  • apologies: I created a question and got an answer: http://stackoverflow.com/questions/26145996/contains-unhappy-with-char13 – whytheq Oct 01 '14 at 18:33
4

To add to what others have said; when I need to embed newlines in T-SQL, I tend to do;

DECLARE @nl CHAR(2) = CHAR(13) + CHAR(10);

..then use @nl as required. That's for Windows line-endings, naturally.

Rory Hunter
  • 3,425
  • 1
  • 14
  • 16
3

Take a look at the Char function. See MSDN. This will help look for the special characters.

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129