时间戳

首页 > 解决方案 > 如何在句子varchar中获取以数字开头的单词

sql - 如何在句子varchar中获取以数字开头的单词

问题描述

这是我的数据示例，请注意该单词可能位于字符串中的任何位置：

   -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(100));
    INSERT INTO @tbl (tokens) VALUES
    ('abc 1XXXX xyz'),
    ('abc xyz 2XXXXXXXX'),
    ('3XX abc xyz');
    -- DDL and sample data population, end

输出应该是：

+----+-----------+
| ID |  Result   |
+----+-----------+
|  1 | 1XXXX     |
|  2 | 2XXXXXXXX |
|  3 | 3XX       |
+----+-----------+

标签： sqlsql-servertsql

解决方案

请尝试以下解决方案。

内置的PARSENAME (Transact-SQL)函数非常适合该任务。

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(100));
INSERT INTO @tbl (tokens) VALUES
('abc 1XXXX xyz'),
('abc 2XXXXXXXX xyz'),
('abc 3XX xyz');
-- DDL and sample data population, end

SELECT ID
    , PARSENAME(REPLACE(tokens, SPACE(1), '.'), 2) AS Result
FROM @tbl;

输出

+----+-----------+
| ID |  Result   |
+----+-----------+
|  1 | 1XXXX     |
|  2 | 2XXXXXXXX |
|  3 | 3XX       |
+----+-----------+

SQL #2，SQL Server 2016 及更高版本

所以球门柱在比赛中途被移动了。这表明最小可重现示例的重要性更高。

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(100));
INSERT INTO @tbl (tokens) VALUES
('abc 1XXXX xyz'),
('abc xyz 2XXXXXXXX'),
('3XX abc xyz');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT * FROM @tbl
    CROSS APPLY (SELECT IIF(TRY_CAST(LEFT(value,1) AS INT) IS NOT NULL, value, NULL) AS token
            FROM STRING_SPLIT(tokens, SPACE(1))) AS t(token)
)
SELECT * 
FROM rs
WHERE token IS NOT NULL;

输出

+----+-------------------+-----------+
| ID |      tokens       |   token   |
+----+-------------------+-----------+
|  1 | abc 1XXXX xyz     | 1XXXX     |
|  2 | abc xyz 2XXXXXXXX | 2XXXXXXXX |
|  3 | 3XX abc xyz       | 3XX       |
+----+-------------------+-----------+

SQL #3，SQL Server 2012 起

DECLARE @separator CHAR(1) = SPACE(1);

SELECT * FROM @tbl
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
            REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
            ']]></r></root>' AS XML).query('
    for $x in /root/r
    return if (xs:int(substring($x,1,1)) instance of xs:int) then $x
         else ()
').value('(r/text())[1]', 'VARCHAR(100)')) AS t(token);

推荐阅读