How to extract this specific number from this set of strings?-CodePudding

I am using SQL Server 2014 and I have a column (called RankDetails) in my table. I need to extract 2 specific numbers which appear in those strings.

Rank Details column:

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

What I am looking for:

       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

I am able to extract the [Rank] column by using the following T-SQL code:

select 

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

from [MyTable]

However, I am having a hard time trying to figure out how to output the [OutOf] column.

I had a look here: SQL - How to return a set of numbers after a specific word for a possible solution but still cannot make it work.

CodePudding user response：

One of possible solutions is:

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;

CodePudding user response：

It's a shame you haven't (yet) upgraded your version of SQL Server as newer versions make this much easier with functions like openJson.

From your sample data you can try the following:

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);

Example DB<>Fiddle

Just for fun a more modern version could work like this:

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;