SQL pattern search

macunte 0
Viewed 47

What 'where' stmt can I use to locate and return values from field cfmesg where the field content is a number followed by hyphen?

sample data 
087-2-1-11
56-080
040-4-2-60
db2 sql
3 Answers

You can try this query

Select * From [TableName] Where [ColumnName] LIKE '%[0-9]-%'

"[0-9]-" will look for numbers followed by a hyphen.

Stack.Developer0

You can use REGEXP_LIKE and use a regular expression to find that pattern:

WITH YOUR_TABLE AS (
  SELECT '087-2-1-11' AS CFMESG 
  FROM sysibm.sysdummy1
  UNION 
  SELECT '56-080'  
  FROM sysibm.sysdummy1
  UNION 
  SELECT 'abcd'  
  FROM sysibm.sysdummy1
) 
SELECT * FROM YOUR_TABLE
WHERE REGEXP_LIKE(CFMESG, '\d-')
CFMESG
087-2-1-11
56-080

dbfiddle

VvdL0

I guess it depends on what you are looking for. If you want to find all lines with a '-' in the value then you would run something like this:

select * from table where locate('-', cfmesg) > 1

If you are looking for something more specific where the beginning part only contains numbers then you'll want something like this:

select * from table where REGEXP_LIKE(cfmesg, '^\d+-')
jwbradley0
Related