Find and show special character codes from nvarchar column

Question

I want to know which special characters (line breaks, tabs, etc) are in my column of type nvarchar. I know how I can select which rows contain a specific character.

e.g. SELECT * FROM your_table WHERE your_column LIKE '%' + CHAR(10) + '%'

However, I want to know which columns contain characters not in [a-zA-Z0-9] and then WHICH characters that are. I also looked if I can just do a select on a column to display RAW text with all characters displayed (similar to Microsoft Word's display all characters for example), but I couldn't find how to do this either.

Answer 1

If you can live with one-for-one character replacement, use the TRANSLATE function.

For example:

DECLARE @T table (c1 nvarchar(50) NULL);

INSERT @T (c1) VALUES 
(N'Hello' + NCHAR(9) + N'there' + NCHAR(10) + NCHAR(13));

SELECT
    TRANSLATE(
        T.c1, 
        -- tab, line feed, carriage return
        CONCAT(NCHAR(9), NCHAR(10), NCHAR(13)), 
        -- corresponding replacement codes
        CONCAT(NCHAR(9225), NCHAR(9226), NCHAR(9229)))
FROM @T AS T;

Output
Hello␉there␊␍

It's up to you to choose the replacement characters. I used standard ones from the Unicode 'Control Pictures' block.

Otherwise, use nested REPLACE calls to replace e.g. a tab character with '{tab}'.

When dealing with nvarchar data, it is important to prefix literals with N to produce Unicode. You should also use the Unicode functions like NCHAR instead of CHAR.