CODEWITHSUNDEEP
mysqlsqlsql-serversql-like
dividedbyzero
dividedbyzero

Reputation: 99

SQL Query to find string that follows a particular pattern

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.

Upvotes: 0

Views: 631

Answers (1)

axnet
axnet

Reputation: 5790

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

Upvotes: 1

Related Questions