获取字符串中的7位数字 [英] Get 7 digit number in string

问题描述

大家好，



我有问题。



我有一个字符串'' b>我星期二要去班加罗尔，我会见你从那里打电话给你。我有5200301号码。'



在上面的字符串中，我想检查是否存在7位数字。



如何在SQL中实现.. ??

Hi to all,

I have problem.

I have a string like ' I am going to bangalore on tuesday, I ll meet you call you from there itself. i have 5200301 number with me. '

In the above string i want to check whether a 7 digit number is existing or not.

How i can achieve in SQL ..??

推荐答案

嗨Jagadish，



你可以尝试这个代码，它的工作正常

Hi Jagadish,

You could try this code, its working fine

DECLARE @temp TABLE
(
      string NVARCHAR(500)
)

INSERT INTO @temp (string)
VALUES
    ('I am going to bangalore on tuesday, I ll meet you call you from there itself. i have 5200301 number with me.'),
    ('I am going to chennai on tuesday, I ll meet you call you from there itself. i have 1234567 number with me.'),
    ('I am going to madurai on tuesday, I ll meet you call you from there itself. i have 123456789 number with me.')

SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1)
FROM (
    SELECT subsrt = SUBSTRING(string, pos, LEN(string))
    FROM (
        SELECT string, pos = PATINDEX('%[0-9]%', string)
        FROM @temp
    ) d
) t where (LEN(LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1))=7)

输出：

---------

| 5200301 |

---------

| 1234567 |

---------



希望这对你有所帮助。



问候，

RK

Output:
---------
| 5200301 |
---------
| 1234567 |
---------

Hope this helps you a bit.

Regards,
RK

最简单的方法来获取数字开头的charindex，

假设它将是字母数字字符串中唯一的数值。



如果没有，找到所有字符，过滤它们直到找到一个正确的长度

Easiest way to get the charindex where the number starts,
assuming it would be the only numerical value in a alpha numeric string.

If not, find all the chars, filter through them until you find one the correct length

PATINDEX ( '%[^0-9]%' , expression)

SQL示例 [ + ]

这篇关于获取字符串中的7位数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！