I'm attempting to isolate eight digits from a cell that contains other numbers as well as text and no rhyme or reason to where it is placed. An example return would look something like this:
will deliver 11/07 in USA at 12:30 with conf# 12345678
I need the conf# only, but it could be at the end, beginning, middle of the string and I don't know how to isolate it. I'm working in DB2 so I can't use functions such as PATINDEX or CHARINDEX, so what are my other option for pulling out only "12345678" regardless of where it is located?
While DB2 doesn't have PATINDEX or CHARINDEX, it does have LOCATE.
If your DB2 version supportx pureXML, you can use the regular expression support in XQuery, something like:
This assumes that 8-digit sequence appears only once in the column. You may need to tweak the regex to support some border cases.