获取字符串中的7位数字 | 获取字符串中的7位数字

本文介绍了获取字符串中的7位数字的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好，

我有问题。

我有一个字符串'' 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 ..??

推荐答案

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

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

[]

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