How can I find a number in a string in DB2 SQL

Question

I need to find a number in a string in DB2 SQL, and I have the position of the first number.

Can anybody help me please?

Answer 1

Assuming you are using Db2 11.1, you should use REGEXP_EXTRACT.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061492.html

E.g.

values regexp_extract(' STR. 5TH PALACE WASHINGTONN, 15','\b[0-9]+\b')

returns

 1
 --
 15

Answer 2

Solution 1

One or more digits not followed by non-space character.
See the REGEXP_SUBSTR description.

select str, regexp_substr(str, '[\d]+(?![^\s])') num
from table(values 
  'STR. WASHINGTONN 15'
, 'STR. WASHINGTONN, 15'
, 'STR. WASHINGTONN NR. 15'
, 'STR. 5TH PALACE WASHINGTONN, 15'
) t(str);

BTW:
Seems that negative lookbehind (?<![^\s])[\d]+(?![^\s]) (one or more digits not preceded by non-space and not followed by non-space) doesn't work on my DB2 7.3 for IBM i at least, but does work on Db2 11.1.

Solution 2

We add spaces to both sides of string, if lookahead/lookbehind regexp functionality doesn't work:

select str, trim(regexp_substr(' '||str||' ', '\s[\d]+\s')) num
from table(values 
  'STR. WASHINGTONN 15'
, 'STR. WASHINGTONN, 15'
, 'STR. WASHINGTONN NR. 15'
, 'STR. 5TH PALACE WASHINGTONN, 15'
) t(str);

Answer 3

You're probably interested in LOCATE or REGEXP_INSTR functions but it's really hard to tell from the information given. REGEXP_INSTR is relatively new so you'll need to check if it's available in your version of Db2 on your platform.