CODEWITHSUNDEEP
sqldb2
Batch Wade
Batch Wade

Reputation: 1

look up any number in a text field

I have a column that has plain text data. I am looking to find any name that is followed by an integer 'Sam 123', Sam 597" etc.

I have tried 

SELECT message, LENGTH(message)
FROM surveys
WHERE LENGTH(MESSAGE) = 6

but it returns alphanumeric data.

Upvotes: 0

Views: 60

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12339

For all DB2 versions:

select message
from table(values 'Sam 123', 'Sam 597', 'Sam123', 'Sam', '123 Sam') t(message)
where xmlcast(xmlquery('fn:matches($s, "[a-zA-Z]+ [0-9]+")' passing t.message as "s") as int) = 1

Upvotes: 1

The Impaler
The Impaler

Reputation: 48865

Starting on DB2 11.1 you can use regular expressions. Your query should look like:

select message, length(message)
  from surveys
  where regexp_like(message, '[a-zA-Z]+ [0-9]+')

This regular expression matches:

  • One or more letters, followed by
  • A single space, followed by
  • One or more digits.

Upvotes: 1

Related Questions