How to pull a string of numbers out of a table tha

问题:

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?

回答1:

While DB2 doesn't have PATINDEX or CHARINDEX, it does have LOCATE.

回答2:

If your DB2 version supportx pureXML, you can use the regular expression support in XQuery, something like:

select xmlcast(
 xmlquery(
   ' if (fn:matches( $YOURCOLUMN, "(^|.*[^\d])(\d{8})([^\d].*$|$)")) then fn:replace( $YOURCOLUMN,"(^|.*[^\d])(\d{8})([^\d].*$|$)","$2") else "" '
 )
 as varchar(20)
)
from YOURTABLE

This assumes that 8-digit sequence appears only once in the column. You may need to tweak the regex to support some border cases.