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