Caret in SQL Server

Question

Consider the below query:

Select  
    Case when left(‘152rew’, 5) not like ‘%[^A-Za-z]%’ 
       then ‘true’ 
       else ‘false’ 
    end

How ^ this is working in above SQL statement as it is returning false.

Can someone help me with this?

asked before: https://stackoverflow.com/questions/29206404/mssql-regular-expression — Luuk, Nov 10 '19 at 09:36
Your like regex logic is asking if the input is _not_ like a string which contains at least one non letter. Clearly, the left-5 contains a digit, so the assertion is false. — Tim Biegeleisen, Nov 10 '19 at 09:38

score 1 · Answer 1 · edited Nov 10 '19 at 13:23

In the LIKE syntax used in SQL Server you can indeed use a character class.

For example:

x LIKE '%[A-Z]%'

Which would be true if x contains at least 1 letter.

Btw, this example assumes a case-insensitive collation.

The ^ at the start of the [...] is used as a negation of that.

F.e.

x LIKE '%[^A-Z]%'

Which would be true if x contains at least one character that is NOT a letter.

So then when negating the negation:

x NOT LIKE '%[^A-Z]%'

It's like looking for x that only contain letters, or is an empty string. In other words: x most NOT contain a character that is NOT a letter.

But something as ‘152rew’ also contains digits, so it's false.

Danyal Sandeelo · Answer 2 · 2019-11-10T10:18:26.290

Select  Case when left('152rew',5) not like '%[^A-Za-z]%' then 'true' else 'false' end

left('152rew',5) would be 152re which doesn't match the regex '%[^A-Za-z]%' because:

'%[^A-Za-z]%' would be matched against the words that contain letters only (A-Z) since it has a-z that would make it case-insensitive so it will accepts all the letters from A to Z i.e capital as well as small.

As it doesn't match '%[^A-Za-z]%' so true is returned.

score 0 · Answer 3 · answered Nov 10 '19 at 10:09

SQL Server allows character classes in LIKE. '%[A-Za-z]%' means "contains a letter". ^ negates the expression. '%[^A-Za-z]%' means "contains a non-letter".

Hence your CASE expression is this in pseudo code:

if '152re' doesn't contain a "non-letter"
     then 'true'
     else 'false'
end if

Or simpler:

if '152re' contains a "non-letter"
     then 'false'
     else 'true'
end if

1, 5, and 2 are not letters, so the string contains a "non-letter"; the expression returns 'false'.

Caret in SQL Server

3 Answers3