info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Migrations, Replication & Cloud



Find hidden characters in a SQL Server table data
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2015-10-07







Step 1:

SET TEXTSIZE 0;
SET NOCOUNT ON;
-- Create the variables for the current character string position & for the character string.
DECLARE @position int, @str varchar(max), @lengthstring int;
SET @str = (Select ObjectName from [dbo].[CounterDetails] where counterid=1);
SET @lengthstring = len(@str);
Select @lengthstring as 'Total String Length to be used as input'; --Get exact string length


Step 2: Input the @lengthstring value into the char function below in order to set exact size.

DECLARE @string char(9);
SET @string = (Select ObjectName from [dbo].[CounterDetails] where counterid=1);
SET @position = 1;
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)) as 'ASCII Value',
CHAR(ASCII(SUBSTRING(@string, @position, 1))) as 'CHAR character'
SET @position = @position + 1;
END;
SET NOCOUNT OFF;
GO


Image 1:





Resources:

ASCII (Transact-SQL)

Data Cleansing

Cleansing Customer Data Using Data Quality Services (DQS)