-1

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?

2
  • 2
    What have you already tried? Can you please show some example data?
    – slhck
    Commented Apr 5, 2019 at 8:31
  • SELECT LENGTH(RTRIM(TRANSLATE('ABCDEF123',' ','0123456789'))) ...but not always work :-(
    – Denis
    Commented Apr 5, 2019 at 9:29

3 Answers 3

3

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
5
  • Thanks a lot for the help ...but I don't know why, I take always a minus sign result on screen. ("-"). Maybe regexp_extract or regexp_substr is another version on the AS400 that I use ? For example this one work: S ELECT REGEXP_SUBSTR('hello 55 aaa to you', '.o',1,2) FROM sysibm.sysdummy1 it give me "to" result. Thanks a lot again
    – Denis
    Commented Apr 9, 2019 at 8:13
  • DB2 for i 71.. and above has the same function ( REGEXP_SUBSTR and REGEXP_EXTRACT are synonyms) ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/… Commented Apr 9, 2019 at 22:26
  • Yes... but I don't undestand why some parameters work and some ot work. Tha samples on IBM site is too low in order to undestand.
    – Denis
    Commented Apr 10, 2019 at 7:23
  • what is not working? Commented Apr 10, 2019 at 21:34
  • The regexp_substr(str, '[\d]+(?![^\s])') for example... and a lot of other parms :-(
    – Denis
    Commented Apr 11, 2019 at 3:53
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);
6
  • Mark...on your query i receive this error: 1 -- The external program or service program returned SQLSTATE 2201S. The text message returned from the program is: REGEXP_SUBSTR [\d]+(?![^\s]) . :-(
    – Denis
    Commented Apr 9, 2019 at 8:17
  • Do you know how can I see the vers. of my DB2 please ?
    – Denis
    Commented Apr 9, 2019 at 8:17
  • Try this: select release_level from qsys2.license_info where product_text='IBM i' and usage_type='*REGISTERED' Commented Apr 9, 2019 at 8:49
  • We have here the V7R2M0 version. Can be this the reason because it didn't work please ? Thanks again
    – Denis
    Commented Apr 9, 2019 at 15:23
  • 1
    You may try the following as well as in Paul's example: regexp_substr(str, '\b[\d]+\b') Commented Apr 9, 2019 at 15:53
0

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.

2
  • I have something like: STR. WASHINGTONN 15 or: STR. WASHINGTONN, 15 or: STR. WASHINGTONN NR. 15 or: STR. 5TH PALACE WASHINGTONN, 15 And I must take out the 15 number I don't know the version of DB2 but the OS of iSeries is 7.2 Thanks a lot. Denis
    – Denis
    Commented Apr 5, 2019 at 12:25
  • How can I use LOCATE but with indentify all numerics chars please ? Thanks again
    – Denis
    Commented Apr 8, 2019 at 6:47

Not the answer you're looking for? Browse other questions tagged .