Using regexp_replace function, we can extract numbers from a string.

Example :

select regexp_replace(‘Nanda has more than 15 years experience in Ibm i’, ‘[^0123456789]’, ‘  ‘, 1, 0, ‘i’)

from sysibm.sysdummy1.

Result :

15

First parm : Source_String

Second Parm : Pattern-expression. [^0123456789] tells the system not to match the numbers.

Third Parm :  Replacement String

Fourth Parm: Start position of the Source String to begin the search

Fifth Parm : Specifies which occurrence of the pattern-expression within source string to search for and replace. 0 indicates that all occurrences of the pattern-expression in source-string are replaced

Sixth Parm: Flags. ‘i’ specifies matching is case insensitive