CODEWITHSUNDEEP
sqlsql-serverregext-sql
naro
naro

Reputation: 416

How to check a database column for special sign

I want to check a column for a special sign with regular expression, but not all special sign. I want all sign that are not letter, number and not ,+.-& (blank) .I try this

where column like '%[^((a-z)(A-Z)(0-9)(,\+\.\-)(\&)( ))]%'

same like this

where column like '%[^0-9a-zA-Z ,\-+\.\& ]%'

But if I try this Statement, I get strings with - and speziell blank.

result1: 't-est regex'

result2: ' TestJ. '

Have you any idea why?

Thx for the help.

Upvotes: 3

Views: 101

Answers (1)

LukStorms
LukStorms

Reputation: 29667

The LIKE operator isn't exactly using the regex syntax. It's way more simplified.

But some things are still possible.

This LIKE would get what you want:

where [column] like '%[^A-Za-z0-9 &.,+-]%'

Note that the class negation ^ does work.
And it's best to put a - at the end of a class. So it's not mistaken to be used for a range of characters.

There's no concept for capture groups in a LIKE, so the ( and ) were removed.
But if you also want the LIKE to ignore the brackets, just add them to the character class.
(even in regex you don't have capture groups inside a character class anyway)

The LIKE operation is case-insensitive when the COLLATION of the character field is case-insensitive.
Which seems to be the default on most databases.
So most of the times, a LIKE '%[A-Z]%' and LIKE '%[A-Za-z]%' would behave the same.

Example snippet:

-- using a Case-Sensitive collation for the varchar. 
declare @T table (id int identity(1,1), col varchar(30) COLLATE Latin1_General_CS_AS);

insert into @T (col) values 
('abc123'),
('ABC & 123'),
('abc123 &.,+-'),
('abc-123 #');

select * from @T where col like '%[^A-Za-z0-9 &.,+-]%';

Returns:

id  col
--  ---------
4   abc-123 #

Upvotes: 1

Related Questions