SQL Query to find string that follows a particular pattern

Question

I have a varchar column in a table and I need to find all values in the column that matches a pattern. The pattern is either parta-partb-partc or parta-partb-positiveInteger-partc. Except for the number part, everything is fixed. For example

**someColumn**

parta-partb-partc
parta-partb-1-partc
parta-partb-1xyz-partc
parta-partb-123-partc
parta-partb-abc-partc

My search query should return

parta-partb-partc
parta-partb-1-partc
parta-partb-123-partc

So far this is what I got

 SELECT * 
 FROM tableName 
 WHERE 
      someColumn ='parta-partb-partc' 
   OR someColumn LIKE 'parta-partb-%[0-9]-partc'

I am not able to construct the LIKE part to get only strings with positive number in between.

hsqlserver for testing but could any among sqlserver, oracle or mysql in production — dividedbyzero
– dividedbyzero, Commented Jun 17, 2020 at 6:28
Function would differ according to the database. If you could suggest a right database, can provide you the solution — Jim Macaulay
– Jim Macaulay, Commented Jun 17, 2020 at 6:40
I can't be sure what database the customer might use. What do you suggest in this case? Should I bring in the values through someColumn LIKE 'parta-partb-%-partc' and use java regular expression to filter out the none required ones? — dividedbyzero
– dividedbyzero, Commented Jun 17, 2020 at 6:55

axnet · Accepted Answer · 2020-06-17 08:22:16Z

1

For MySQL following query gives expected result

SELECT *
FROM
  tableName
WHERE
   someColumn = 'parta-partb-partc'
   OR 
   someColumn REGEXP 'parta-partb-[0-9]+-partc'
;

For Oracle following query gives expected result

SELECT *
FROM tableName
WHERE
   someColumn = 'parta-partb-partc'
   OR 
   REGEXP_LIKE(someColumn, 'parta-partb-[[:digit:]]+-partc')
;

For MS-SQL unfortunately REGEX is not supported in where clause, so you have to do something like following. [Specific to your example data]

SELECT *
FROM tableName
WHERE
   someColumn = 'parta-partb-partc'
   OR 
   (    someColumn LIKE 'parta-partb-[0-9]-partc'
    OR  someColumn LIKE 'parta-partb-[0-9]%[0-9]-partc'
    AND someColumn NOT LIKE 'parta-partb-[0-9]%[a-zA-Z]%[0-9]-partc'
    AND someColumn NOT LIKE 'parta-partb-[0-9]%[a-zA-Z]-partc'
   )

edited Jun 17, 2020 at 8:22

answered Jun 17, 2020 at 6:53

axnet

5,8303 gold badges30 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query to find string that follows a particular pattern

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related