SQL Regex String Matching of Two Words

Question

I'm trying to write a query to show the rows containing a code that begins with 11-digit number followed by -1 or -2 then _DataSheet or _RoHS

Examples

02704198976-1_DataSheet
03448106681-1_RoHS

Query

SELECT m.PK, m.code
FROM medias m
WHERE m.code LIKE CONCAT(REPLICATE('[0-9]', 11),'-[1-2]_(DataSheet|RoHS)%')

Results

0 rows returned

If I change the WHERE clause, then it shows too may results.

Query

SELECT m.PK, m.code
FROM medias m
WHERE m.code LIKE CONCAT(REPLICATE('[0-9]', 11),'-[1-2]_%')

Results

02704198976-1_DataSheet
03448100857-1_SDS 
03448106681-1_RoHS

• ypercubeᵀᴹ: Is that % in the end required or not?
• A_B: @yper-trollTM Yes, since there may be characters after those words

Answer 1

Without regex, I think you need an OR for the last requirement (for the part that is either DataSheet or RoHS:

WHERE m.code LIKE CONCAT(REPLICATE('[0-9]', 11), '-[1-2][_]DataSheet%')
 OR m.code LIKE CONCAT(REPLICATE('[0-9]', 11), '-[1-2][_]RoHS%')

Answer 2

Per https://technet.microsoft.com/en-us/library/ms179859(v=sql.110).aspx LIKE on sql server does not deal with () constructs as a regex would do, hence the failure of your first query.

This command is too simple for your problem, you need a true regex, which does not seem to exist for SQL Server natively.

You may rewrite your latest query adding:

AND (m.code LIKE '%_DataSheet' OR m.code LIKE '%_RoHS')

and it should do the job, even if not pretty, nor 100% correct (as you can't anchor the strings at the end), nor easy to extend in a sane way...