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
This question is related to
sql
sql-server
The answer is
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.
Source: Stackoverflow.com