like pattern '[0-9][0-9][0-1][0-9]'

Question

I have a nvarchar field with 4 digits. The first 2 digits are between 00 and 99. The next 2 digits should be a number between 00 and 15

My SQl like looks like this:

I4020 like '[0-9][0-9][0-1][0-9]'

The problem is that e.g. 1219 is possible.

Is it possible to do this with the like pattern?

BR Stefan

Answer 1

It's not possible to do this with one LIKE pattern. However, you can use two of them:

(I4020 like '[0-9][0-9]0[0-9]' OR I4020 like '[0-9][0-9]1[0-5]')

Alternatively, if the field always contains digits, you can use numerical matching:

I4020 LIKE '[0-9][0-9][0-9][0-9]'
AND CONVERT(int, RIGHT(I4020, 2)) <= 15

Note, though, that T-SQL AND does not short-circuit: The latter query might fail if the two rightmost letters of I4020 are not numeric.