I was reading the article at mssqltips and wanted to try the caret in regex. I understand regex pretty well and use it often, although not much in SQl Server queries.

For the following list of names, I had thought that 1) select * from people where name like '%[^m]%;' will return those names that do not contain 'm'. But it doesn't work like that. I know I can do 2) select * from people where name not like '%m%'; to get the result I want, but I'm just baffled why 1) doesn't work as expected.

1. Amy
2. Jasper
3. Jim
4. Kathleen
5. Marco
6. Mike
7. Mitchell

I am using SQL Server 2017, but here is a fiddle:

sql fiddle

CodePudding user response：

'%[^m]%' would be true for any string containing a character that is not m. An expanded version would be '%[Any character not m]%'. Since all of those strings contain a character other than m, they are valid results.

If you had a string like mmm, where name like '%[^m]%' would not return that row.