Tales from Dr Carl – Uncloaking pesky hidden characters

In the post https://popbi.wordpress.com/2012/08/28/how-to-remove-carriage-return-line-feed-tab-from-a-string-in-tsql I discussed options for removing unprintable characters. In the code example below, Dr Carl demonstrates how to use the ASCII function to unclock those pesky characters likely to cause troubles in your BI solution.  The result of the ASCII function is the specific character code you can then reference with the char command to include in your removal procedure.  A complete list of string functions is referenced here http://msdn.microsoft.com/en-us/library/ms181984.aspx.

--Create SomeTable
CREATE TABLE dbo.SomeTable
(column1 varchar(10))
--Insert a value
INSERT dbo.SomeTable

--Insert a dubious value with a tab character at the end
INSERT dbo.SomeTable
('Value' + CHAR(9))

--Values look the same at first
SELECT Column1, LEN(column1) AS ColumnLength FROM dbo.SomeTable

--Unclock the dubious character
SELECT Column1, LEN(Column1) AS ColumnLength,
 ASCII(RIGHT(Column1,1)) as AsciiCharacter
 FROM dbo.SomeTable

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s