Pattern matching with SQL As for a range of characters

advertisements

Is there a way to use Pattern Matching with SQL LIKE, to match a variable number of characters with an upper limit?

For example, I have one column which can have "correct values" of 2-10 numbers, anything more than 10 and less than 2 is incorrect.


If you really want to use like you can do:

where col like '__' or col_like '___' or . . .

or:

where col like '__%'  and        -- at least two characters
      col not like '_________%'  -- not 9 characters

The more typical method would be:

where len(col) between 2 and 10

If you want to ensure they are numbers:

where len(col) between 2 and 10 and
      col not like '%[^0-9]%'