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