Kornél Regius Kornél Regius - 5 months ago 30
SQL Question

SQL LIKE - Using square bracket (character range) matching to match an entire word

In REGEX you can do something like [a-c]+, which will match on

  • aaabbbccc

  • abcccaabc

  • cbccaa

  • b

  • aaaaaaaaa

In SQL LIKE it seems that one can either do the equivalent of ".*" which is "%", or [a-c]. Is it possible to use the +(at least one) quantifier in SQL to do [a-c]+?

EDIT: Just to clarify, the desired end-query would look something like

SELECT * FROM table WHERE column LIKE '[a-c]+'

which would then match on the list above, but would NOT match on e.g "xxxxxaxxxx"

Answer Source

As a general rule, SQL Server's LIKE patterns are much weaker than regular expressions. For your particular example, you can do:

where col not like '%[^a-c]%'

That is, the column contains no characters that are not a, b, or c.