如何从随机放置的表中拉出一串数字 [英] How to pull a string of numbers out of a table that are placed randomly

问题描述

我试图从一个单元格中分离出八位数字，其中包含其他数字以及文字，也没有放置放置的韵律或理由。一个示例返回将如下所示：

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 

我只需要conf＃，但它可能是最后，开始，中间的字符串和我不知道如何隔离它。我在DB2中工作，所以我不能使用诸如PATINDEX或CHARINDEX之类的功能，所以我的其他选项只能拉出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?

推荐答案

如果您的DB2版本支持pureXML，则可以使用XQuery中的正则表达式支持，如：

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

假设8位序列在列中只显示一次。您可能需要调整正则表达式来支持一些边界案例。

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

这篇关于如何从随机放置的表中拉出一串数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！