Using Regexpression in Microsoft SQL not like

I am trying to perform a regex match in Microsoft SQL Server Management Studio 2022 v18.12.1 to query that looks as follows:
SELECT * FROM *table* WHERE [Item] LIKE '[H][0-9][0-9]%'

[H][0-9][0-9]% will match start with letter H only and [0-9] can matches any number from 0-9.I put twice mean will get two number after H, % will match Any string, because I want to get item like H03.1.

Here is my datatable:

id Item
1 H23
2 H03.1
3 H030BZZ
4 H03YYZZ
5 H2261ZZ
6 H51Q1ZZ

I want to only match H[0-9][0-9] but not match any letter except letter H.
For example: I want to get H23 and H03.1 but not get H030BZZ, H03YYZZ, H2261ZZ, H51Q1ZZ.

I have tried the code like:

WHERE [Item]  NOT LIKE '[H][0-9]{0,3}[A-Z]{0,3}

but still can’t achieve my goal.Any suggestions would be helpful. Thank you.

>Solution :

You could use this query to check first if the Item starts with H[0-9][0-9] and then check that it doesn’t include a letter other than H after the first 3 characters:

SELECT * 
FROM table1 
WHERE [Item] LIKE '[H][0-9][0-9]%'
  AND [Item] NOT LIKE '___%[A-GI-Z]%'

Output (for your sample data):

id  Item
1   H23
2   H03.1

Demo on dbfiddle

Leave a Reply