如何从这组字符串中提取这个特定的数字?

Question

我使用SQL Server 2014，我有一个列(称为RankDetails)在我的表。我需要提取出现在这些字符串中的2个特定数字。

Rank Details列:

RankDetails
#1 of 223 hotels in Maldives
#3 of 223 hotels in Maldives
...
#10 of 223 hotels in Maldives
...
#126 of 223 hotels in Maldives

我在找什么:

RankDetails                Rank         OutOf
#1 of 223 hotels in Maldives       1            223
#3 of 223 hotels in Maldives       3            223
...                                ...          ...
#10 of 223 hotels in Maldives      10           223
...                                ...          ...
#126 of 223 hotels in Maldives     126          223

我能够通过使用以下T-SQL代码提取[Rank]列:

select 
Rank = SUBSTRING([RankDetails], PATINDEX('%[0-9]%', [RankDetails]), PATINDEX('%[0-9][a-z !@#$%^&*(()_]%', [RankDetails]) - PATINDEX('%[0-9]%', [RankDetails]) + 1),
*
from [MyTable]

然而，我很难弄清楚如何输出[out]列。

我在这里看了一下:SQL -如何在一个可能的解决方案的特定单词后返回一组数字，但仍然不能使其工作。

Answer 1

很遗憾你还没有升级你的SQL Server版本，因为新版本使这更容易像openJson。

从您的示例数据中，您可以尝试以下操作:

select RankDetails,
Try_Convert(int, Replace(Left(rankdetails, NullIf(o, 0)-1), '#', '')) [Rank],
Try_Convert(int, Substring(rankdetails, o+3, l)) OutOf
from t
cross apply (values(CharIndex('of ', rankdetails) ))v(o)
cross apply (values(CharIndex('hotels', rankdetails) -o -4))h(l);

,DB&lt例子的在小提琴

只是为了好玩，一个更现代的版本可以这样工作:

select RankDetails, 
Max(case when k=0 then v end) [Rank],
Max(case when k=2 then v end) OutOf
from t
cross apply (
select Try_Convert(int,Replace(value,'#',''))v, [key] k
from OpenJson(Concat('["',replace(rankdetails, ' ', '","'),'"]'))
)x
group by RankDetails;

Answer 2

您的数据

CREATE TABLE test
(
id          INT IDENTITY PRIMARY KEY,
rankdetails VARCHAR(100)
);
INSERT INTO test
VALUES      ('#1 of 223 hotels in Maldives'),
('#3 of 223 hotels in Maldives'),
('#10 of 223 hotels in Maldives'),
('#126 of 223 hotels in Maldives');  

首先通过使用SUBSTRING,CHARINDEX和REPLACE函数获得排名和下一个字符

select  
Id,
RankDetails           
,REPLACE(SUBSTRING (RankDetails,0,CHARINDEX(' ',RankDetails)), '#', '') Rank
,SUBSTRING(RankDetails,CHARINDEX(' ',RankDetails)+1,LEN(RankDetails)) details
FROM test

<表类>IdRankDetails排名细节1在马尔代夫223家酒店中排名第一1在马尔代夫223家酒店中排名第一2在马尔代夫223家酒店中排名第33在马尔代夫223家酒店中排名第33在马尔代夫223家酒店中排名第10在马尔代夫223家酒店中排名第104在马尔代夫223家酒店中排名126在马尔代夫223家酒店中排名126

一个可能的解决方案是:

DECLARE @t TABLE (
Id          INT          NOT NULL PRIMARY KEY,
RankDetails VARCHAR(100) NOT NULL
);
INSERT INTO @t (Id, RankDetails) VALUES (1, '#126 of 223 hotels in Maldives');
INSERT INTO @t (Id, RankDetails) VALUES (2, '#10 of 223 hotels in Maldives');

WITH Words AS
(
SELECT Id,
Word       = REPLACE(value, '#', ''),
WordNumber = ROW_NUMBER () OVER (PARTITION BY Id ORDER BY (SELECT 1))
FROM @T
CROSS APPLY STRING_SPLIT(RankDetails, ' ')
)
SELECT Id,
Rank  = MAX(IIF(WordNumber = 1, Word, NULL)),
OutOf = MAX(IIF(WordNumber = 3, Word, NULL))
FROM Words
WHERE WordNumber IN (1, 3)
GROUP BY Id;

请尝试以下解决方案。

它使用XML和XQuery进行标记化。

不需要解析，搜索，应用任何条件逻辑等

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (Id int IDENTITY PRIMARY KEY, RankDetails VARCHAR(100));
insert into @tbl VALUES 
('#1 of 223 hotels in Maldives'),
('#3 of 223 hotels in Maldives'),
('#10 of 223 hotels in Maldives'),
('#126 of 223 hotels in Maldives');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = SPACE(1);
SELECT t.*
, REPLACE(c.value('(/root/r[1]/text())[1]', 'VARCHAR(10)'),'#','') AS [Rank]
, c.value('(/root/r[3]/text())[1]', 'INT') AS [OutOf]
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
REPLACE(RankDetails, @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML)) AS t1(c);

+----+--------------------------------+------+-------+
| Id |          RankDetails           | Rank | OutOf |
+----+--------------------------------+------+-------+
|  1 | #1 of 223 hotels in Maldives   |    1 |   223 |
|  2 | #3 of 223 hotels in Maldives   |    3 |   223 |
|  3 | #10 of 223 hotels in Maldives  |   10 |   223 |
|  4 | #126 of 223 hotels in Maldives |  126 |   223 |
+----+--------------------------------+------+-------+

SP:

示例表

CREATE TABLE STUDENT_GRADE
(
STD_ID INT
,FULL_GRADE VARCHAR(200)
)
INSERT INTO STUDENT_GRADE VALUES (1,'#1 OF 50 STUDENTS IN CLASS')
INSERT INTO STUDENT_GRADE VALUES (2,'#2 OF 50 STUDENTS IN CLASS')
INSERT INTO STUDENT_GRADE VALUES (3,'#3 OF 50 STUDENTS IN CLASS')
INSERT INTO STUDENT_GRADE VALUES (4,'#4 OF 50 STUDENTS IN CLASS')
INSERT INTO STUDENT_GRADE VALUES (5,'#5 OF 50 STUDENTS IN CLASS')
SELECT *FROM STUDENT_GRADE
CREATE PROCEDURE SP_STUDENT_GRADE
AS
BEGIN
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP 
CREATE TABLE #TEMP (FULL_GRADE VARCHAR(100), RANKING VARCHAR(20), TOTAL VARCHAR(20))
DECLARE @STRING VARCHAR(100)
DECLARE @RANKING VARCHAR(20)
DECLARE @TOTAL VARCHAR(20)
DECLARE @ID INT = 1
DECLARE @FOR_LOOP INT
SET @FOR_LOOP = (SELECT COUNT(*) FROM STUDENT_GRADE)
WHILE @ID <= @FOR_LOOP
BEGIN
SET @STRING = (SELECT FULL_GRADE FROM STUDENT_GRADE WHERE STD_ID = @ID)
SET @RANKING = (SELECT SUBSTRING(@STRING,2,CHARINDEX(' ',@STRING)-1))
DECLARE @STRING_TOTAL VARCHAR(100)
SET @STRING_TOTAL = (SELECT SUBSTRING(@STRING,CHARINDEX(' ',@STRING)+1,LEN(@STRING)))
DECLARE @STRING_TOTAL2 VARCHAR(100)
SET @STRING_TOTAL2 = (SELECT SUBSTRING(@STRING_TOTAL,CHARINDEX(' ',@STRING_TOTAL)+1,LEN(@STRING_TOTAL)))
SET @TOTAL = (SELECT SUBSTRING(@STRING_TOTAL2,1,CHARINDEX(' ',@STRING_TOTAL2)-1))

INSERT INTO #TEMP VALUES 
(@STRING, @RANKING, @TOTAL)
SET @ID = @ID +1
END
SELECT *FROM #TEMP
END
SP_STUDENT_GRADE