How do I check if a VARCHAR column has control characters in it?

How do I check if a VARCHAR(MAX) column has ASCII Control Characters in SQL Server?

Specifically I am looking for the presence of 0x01 SOH. Will this code work, it is copied from an example where they check for LF character i.e CHAR(13)

SELECT * FROM notes where content LIKE '%' + CHAR(1) + '%';

In any case I would like to get a generic query for all the Control Characters instead of a hard-coding like this.

Well I found others that might have had a similar questions,

Before I go on... dont forget about Collation.

That alone can make your life a nightmare,
If you have differences between Server level / Database level / Column level / Expression level, can cause strange behaviour and incorrect query results,if not properly managed.

I would suggest a regex expression to find what you are looking for.

Found on: How to compare Unicode characters in SQL server?

SELECT COUNT(*) FROM t_question WHERE question LIKE N'%é%'

Found on: How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?:

CREATE TABLE dbo.Numbers
(
    number  INT NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (number)
)
GO
DECLARE @i INT

SET @i = 0

WHILE @i < 1000
BEGIN
    INSERT INTO dbo.Numbers (number) VALUES (@i)

    SET @i = @i + 1
END
GO

SELECT *,
    T.ID, N.number, N'%' + NCHAR(N.number) + N'%'
FROM
    dbo.Numbers N
INNER JOIN dbo.My_Table T ON
    T.description LIKE N'%' + NCHAR(N.number) + N'%' OR
    T.summary LIKE N'%' + NCHAR(N.number) + N'%'
and t.id = 1
WHERE
    N.number BETWEEN 127 AND 255
ORDER BY
    T.id, N.number
GO

Some Documentation from Microsoft: Pattern Matching in Search Conditions

And alternatively: Search Text with Regular Expressions

In my experince with mysql there are build in functions like so:

SELECT '\0x01' REGEXP '[[:graph:]]'; -- returns true
SELECT '\0x01' REGEXP '[[:print:]]'; -- returns false

As a start, I Hope this helps!

StackExchange Database Administrators Q#156519, answer score: 3