MSSQL Regex data filtring

Question

I'm trying to filter data from my table using regex pattern in SQL Management Studio 2008 R2.

My table "Asset" contain the column "Asset_n" which contains the asset names. The goal is to be able to filter five types of names only:

1. contains only five numerical characters e.g 12345
2. contains only six numerical characters e.g 123456
3. contains five numerical characters and end with one or two no numerical e.g. 12345a or 12345ab
4. contains six numerical characters and end with one or two no numerical e.g. 123456a or 123456ab

Here is my query:

SELECT * FROM Asset WHERE asset_n LIKE [\d{5,6}\w{1,2}]

The query return nothing... I have used the same regex pattern in PowerShell and it's working perfectly. SQL is something new for me so I don't realy know how to build my regex pattern for SQL query's.

Thanks in advance for your help.

Answer 1

SQL Server doesn't support regex in LIKE argument, however you could do what you want using the following query:

SELECT * FROM Asset WHERE 
asset_n LIKE '[0-9][0-9][0-9][0-9][0-9]'
OR asset_n LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' 
OR asset_n like '[0-9][0-9][0-9][0-9][0-9][a-z]' OR asset_n like '[0-9][0-9][0-9][0-9][0-9][a-z][a-z]' 
OR asset_n like '[0-9][0-9][0-9][0-9][0-9][0-9][a-z]' OR asset_n like '[0-9][0-9][0-9][0-9][0-9][0-9][a-z][a-z]' 

Answer 2

Try this:

select * from Asset 
where left(asset_n, 5) not like '%[^0-9]%' and 
      (SUBSTRING(asset_n, 6, 4) like '[0-9][a-z][a-z]' or
       SUBSTRING(asset_n, 6, 4) like '[0-9][a-z]' or
       SUBSTRING(asset_n, 6, 4) like '[0-9]' or
       SUBSTRING(asset_n, 6, 4) = '')

Why 4? Because of you have maximum length of 8 charachters and you want to filter those whose length are greater.