1

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.

1
  • 2
    Can you show in your question what you would like the output to look like, in general? Perhaps also give a complete list of the things you need revealed.
    – Paul White
    Jun 18 at 12:45

1 Answer 1

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.