SQL pattern search-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You can try this query

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

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