MSSQL pattern to match all numbers between [1-999] [closed]

Question

I want a SQL pattern expression to match all numbers between 1 and 999.

EDIT:

In MSSQL.

Answer 1

When using the LIKE operator pattern-matching in SQL for a character class, there's no wildcard repeat of the character class like there is in regex. In other words, you can't do things like [0-9]+ or [0-9]{1,3} (both of these would also capture 0)

So, you have to zero-pad the number before you can compare it.

It's not directly a pattern, but this expression relies in part on the LIKE operator and will work for positive integers between 1 and 999:

RIGHT('00' + myfield, 3) LIKE '[0-9][0-9][0-9]'
AND RIGHT('00' + myfield, 3) <> '000'
AND LEN(myfield) <= 3

Edit: assumption made that you're talking about Microsoft SQL Server, since you didn't specify.

Answer 2

SELECT * FROM table WHERE field BETWEEN 1 AND 999;

---

EDIT: This will work in PostgreSQL only.

If you're looking for a regexp pattern to match strings, then something like this:

SELECT * FROM table WHERE field ~ '[1-9][0-9]{1,2}';

Check out documentation on regexp patterns.

SQL Server doesn't support regular expressions as-is.
You can use the following workaround or this question: Regular Expressions in SQL Server servers?.