0

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
2
  • Is that % in the end required or not? Dec 18, 2017 at 20:55
  • @yper-trollᵀᴹ Yes, since there may be characters after those words
    – A_B
    Dec 18, 2017 at 21:03

2 Answers 2

3

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%')
1

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.