Using REPLACE in SQL Query for newline / carriage characters

Question

I have a database called Account Type that has carriage returns and newlines (CHAR (10) and CHAR (13)).

When I look for this value, I need to do REPLACE as shown below. The following code is working 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 need such a replacement?

Note. The data type for the VARCHAR column.

Note. The query is launched from SQL Server Management Studio

Answer 1

Perhaps there are built-in tabs ( CHAR(9) ), etc. You can find out what other characters you need to replace (we don’t know 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 think about better clearing this data before it gets into your database. Cleaning it every time you need to search or display is not the best approach.