Helping you solve those tough coding problems!

Finding non-printable characters in MSSQL Finding non-printable characters in MSSQL

Published on Aug 1, 2019 by Jamie Munro

String fields in MSSQL accept non-printable characters such as tabs, line breaks, etc. When your application has not prevented this characters from being removed and you want to determine if these characters exist in your field, the UNICODE is your friend to answer this question.




The UNICODE returns an ASCII integer for the character passed into the function. Here is the most simplistic example to see what it returns:


SELECT UNICODE(' ') AS [TabChar]


The character inside is a tab. Running the query above returns: 9, a horizontal tab. Here is a list of non-printable ASCII characters. In my case I care about all characters that are less than 32. 33 is a space, but I can live with trimming them, unless it is important to find leading and trailing spaces.

The UNICODE function accepts a string of more than one character; however, it only returns against the first character. So to get the best result and search every character of our string, I will write a while loop.


DECLARE @position int = 1,
@stringToSearch varchar(255) = 'tab and a
line break'

WHILE @position <= LEN(@stringToSearch)

BEGIN
DECLARE @currentChar char(1) = SUBSTRING(@stringToSearch, @position, 1)
DECLARE @currentAsciiValue int = UNICODE(@currentChar)

IF @currentAsciiValue < 32
SELECT @currentAsciiValue as AsciiValue
, STRING_ESCAPE(@currentChar, 'json') as AsciiCharacter
, @position as CharacterPosition

SET @position = @position + 1
END


I wrote this with verbosity in mind to provide more detail about what the values are. The results of this will return the following:
9	\t	4
9 \t 5
13 \r 12
10 \n 13
9 \t 14

Good luck, this took me a while to build towards this final solution from incremental research of different functions and hopefully save someone the time.

Tags: SQL | mssql | unicode

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP