1

I have these values 

'12345/1'
'23456/1'
'34567/1'

But occasionally these are submitted

'a1234/1'

How can I explicitly return results where the values are all numbers? I currently am doing this

SELECT SID FROM TRACE WHERE SID LIKE '_____%'

Thanks in advance!

OVO
  • 123
  • 7
  • 23
  • not sure if this will work in db2 but in mssql you can use something like `WHERE SID NOT LIKE '%[A-Z]%'` – JamieD77 Apr 05 '16 at 18:49
  • Could any character be a letter, or just the first one as in your example? – Joachim Isaksson Apr 05 '16 at 18:52
  • Possible duplicate of [Test for numeric value?](http://stackoverflow.com/questions/8565275/test-for-numeric-value) – mustaccio Apr 05 '16 at 19:06
  • See also [this](http://stackoverflow.com/questions/8565275/test-for-numeric-value/8566231#8566231), [this](http://stackoverflow.com/questions/23489752/determine-if-zip-code-contains-numbers-only/23489951#23489951) and [this](http://stackoverflow.com/questions/33740890/how-to-pull-a-string-of-numbers-out-of-a-table-that-are-placed-randomly) – mustaccio Apr 05 '16 at 19:07

1 Answers1

0

DB2 doesn't support regular expressions. But, you can do what you want using translate():

where translate(sid, '/', '/0123456789') = '/'

This removes all digits from the string . . . and then checks if you are just left with a single slash.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    No, this replaces the zero with a slash and removes all other characters. Should be `'/0123456789'` instead. But I don't think DB2 treats `''` as `NULL` and the syntax is different from Oracle, switches *to* and *from*, so this should work: `translate(sid, '', '0123456789')` . – dnoeth Apr 05 '16 at 19:45