如何在varchar中找到5位数字? [英] How to find 5 digit number in a varchar?

问题描述

基本上我只需要得到一个由空格分隔的 5 位数字.5 位数字可以在 varchar 中的任何位置.

Basically I just need to get a 5 digits number that is separated by a space. The 5 digits number can be anywhere in the varchar.

示例:我在 SQL 2008 表中有一个包含各种数据的 varchar 列

Example: I have a varchar column with this various data in SQL 2008 table

travel visa 34322 LLL001
Coffee 34332 Jakarta 
FDR001 34312 Taxi cost cash
taxi cash 34321
34556 eating dinner with customer
eating dinner 34256 with customer
visa cost 34221 REF773716637366

5 位数字可以在任何地方用空格隔开什么是最好的提取这个?

the 5 digits number can be anywhere separated by a space what is best to extract this?

34322
34332
34312
34556
34256
34221

谢谢

这样的行应该返回空白

Visa refNbr 778738878

尝试了以下但没有运气

SELECT  pjtran.tr_comment 
,substring(pjtran.tr_comment,PATINDEX('%[0-9]%',pjtran.tr_comment),5)
,Left(SubString(pjtran.tr_comment, PatIndex('%[0-9.-]%', pjtran.tr_comment), 50),PatIndex('%[^0-9.-]%', SubString(pjtran.tr_comment, PatIndex('%[0-9.-]%', pjtran.tr_comment), 50) + 'X')-1)
,len(pjtran.tr_comment)-len(replace(pjtran.tr_comment,' ',''))

我想我需要结合使用计算varchar中的空间数.和以上.但我不知道该怎么做

I think I need to use a combination of counting the number of space in the varchar. and the above. but I am not sure how to do it

推荐答案

这样的事情怎么样?

 select substring(tr_comment, patindex('%[0-9][0-9][0-9][0-9][0-9] %', tr_comment), 5) as zip5

如果你想考虑它可能在字符串的末尾:

If you want to consider that it might be at the end of the string:

 select substring(tr_comment, patindex('%[0-9][0-9][0-9][0-9][0-9] %', tr_comment + ' '), 5
                 ) as zip5

这篇关于如何在varchar中找到5位数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！