7

I have a database filed named Account Type that has carriage return and newline characters in it (CHAR(10) and CHAR(13)).

When I search for this value I need to do a REPLACE as shown below. The following code works fine.

SELECT AccountNumber,AccountType, 
  REPLACE(REPLACE(AccountType,CHAR(10),'Y'),CHAR(13),'X') FormattedText 
FROM Account 
WHERE AccountNumber=200
AND REPLACE(REPLACE(AccountType,CHAR(10),' '),CHAR(13),' ') LIKE 
'%Daily Tax Updates:  -----------------        Transactions%'

My question is – what are the other characters (similar to CHAR(10) amd CHAR(13)) that would need such a replace?

Note: Data type for the column is VARCHAR.

Note: The query is run from SQL Server Management Studio

enter image description here

LCJ
  • 22,196
  • 67
  • 260
  • 418
  • 3
    We don't know your data or your requirements, so how are we supposed to know what characters you need to replace? –  Feb 12 '13 at 15:46
  • @dan1111. User can enter any data into the database. So what all character need such a REPLACE. Or is there a better method than REPLCAE? – LCJ Feb 12 '13 at 15:47
  • 2
    well, what characters do you want to allow? It is perfectly valid to store any characters, including newlines, in a field. If there are ones you don't want to allow, that is because of your design requirements (which we don't know about). –  Feb 12 '13 at 15:58
  • 3
    I like how this is one of the top search results for replacing carriage returns in SQL Server, and it's not a real question.... – Ellesedil Oct 07 '14 at 14:28

1 Answers1

8

There are probably embedded tabs (CHAR(9)) etc. as well. You can find out what other characters you need to replace (we have no idea what your goal is) with something like this:

DECLARE @var NVARCHAR(255), @i INT;

SET @i = 1;

SELECT @var = AccountType FROM dbo.Account
  WHERE AccountNumber = 200
  AND AccountType LIKE '%Daily%';

CREATE TABLE #x(i INT PRIMARY KEY, c NCHAR(1), a NCHAR(1));

WHILE @i <= LEN(@var)
BEGIN
  INSERT #x 
    SELECT SUBSTRING(@var, @i, 1), ASCII(SUBSTRING(@var, @i, 1));

  SET @i = @i + 1;
END

SELECT i,c,a FROM #x ORDER BY i;

You might also consider doing better cleansing of this data before it gets into your database. Cleaning it every time you need to search or display is not the best approach.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks. Is there a better method other than `REPLACE`? Can't we add `N` before the parameter value rather than using REPLACE? – LCJ Feb 12 '13 at 16:00
  • 1
    @Lijo I don't understand. Do you expect an N prefix to magically know all the characters you don't like, and make them disappear? Can you please go back to the question and further clarify exactly what you're trying to do? – Aaron Bertrand Feb 12 '13 at 16:03