Regex for a serial number in T-SQL

Question

I'm a novice at regexs and am currently trying to come up with a simple regex that searches for a serial number in the following format: 0217103200XX, where "XX" can each be a numeric digit. I'm using SQL Server Management Studio to pass the regex as a parameter in a stored procedure. I'm not sure if the syntax is any different from other programming languages. I have the following regex as a reference: (?:2328\d\d(?:0[1-9]|[1-4]\d|5[0-3])\d{4})

Any suggestions are appreciated.

UPDATE: I'm actually using this in a SQL Query and not in a .Net application. The format is as follows:

USE [MyDB]

EXEC MyStoredProcedure @regex = '(?:2328\d\d(?:0[1-9]|[1-4]\d|5[0-3])\d{4})'

Answer 1

As OMG Ponies stated - SQL Server does not natively support regex (need to use SQLCLR for 2005+, or xp_cre).

If I have understood your question, you could use a PATINDEX to find the serial numbers

Select *
From dbo.MyTable
Where PATINDEX('0217103200[0-9][0-9]', SerialNumberColumn) > 0

Answer 2

Use LIKE: there is no native RegEx in SQL Server

LIKE '0217103200[0-9][0-9]'