我正在使用 SQL Server 2014，并且我的表中有一個列（稱為 RankDetails）。我需要提取出現在這些字串中的 2 個特定數字。
排名詳情欄：
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]
但是，我很難弄清楚如何輸出 [OutOf] 列。
我在這里看了一下：SQL - 如何在特定單詞之后回傳一組數字以獲得可能的解決方案，但仍然無法使其作業。
uj5u.com熱心網友回復：
很遺憾，您還沒有（還）升級您的 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<>Fiddle
只是為了好玩，更現代的版本可以這樣作業：
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;
uj5u.com熱心網友回復：
一種可能的解決方案是：
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;
uj5u.com熱心網友回復：
請嘗試以下解決方案。
它使用 XML 和 XQuery 進行標記化。
無需決議、搜索、應用任何條件邏輯等。
SQL
-- 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 |
---- -------------------------------- ------ -------
uj5u.com熱心網友回復：
你的資料
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
| ID | 排名詳情 | 秩 | 細節 |
|---|---|---|---|
| 1 | 在 馬爾代夫 的 223 家酒店中排名第 1 | 1 | 馬爾代夫的 223 家酒店 |
| 2 | 在 馬爾代夫 的 223 家酒店中排名第 3 | 3 | 馬爾代夫的 223 家酒店 |
| 3 | 在 馬爾代夫 的 223 家酒店中排名第 10 | 10 | 馬爾代夫的 223 家酒店 |
| 4 | 在 馬爾代夫 的 223 家酒店中排名第 126 | 126 | 馬爾代夫的 223 家酒店 |
然后用于Subquery從詳細資訊列中獲取值
select Id,
RankDetails,
Rank,
Left(SubString(details, PatIndex('%[0-9.-]%', details), 8000),
PatIndex('%[^0-9.-]%', SubString(details, PatIndex('%[0-9.-]%', details), 8000) 'X')-1) OutOf
from
(select
Id,
RankDetails
,REPLACE(SUBSTRING (RankDetails,0,CHARINDEX(' ',RankDetails)), '#', '') Rank
,SUBSTRING(RankDetails,CHARINDEX(' ',RankDetails) 1,LEN(RankDetails)) details
FROM test) t
小提琴手
uj5u.com熱心網友回復：
帶有 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482499.html