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

Question

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.

Stu · Answer 1 · 2022-05-28T20:26:48.317

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;

score 0 · Answer 2 · answered May 28 '22 at 19:58

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;

string_split (as used) does NOT guarantee order. `ORDER BY (SELECT 1)` That is logical nonsense. This might seem to work - but there is no guarantee. — SMor, May 28 '22 at 20:16

score 0 · Answer 3 · answered May 29 '22 at 01:35

Please try the following solution.

It is using XML and XQuery for tokenization.

No need to parse, search, apply any conditional logic, etc.

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

Output

+----+--------------------------------+------+-------+
| 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 |
+----+--------------------------------+------+-------+

score 0 · Answer 4 · edited May 29 '22 at 08:41

Sample table with 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

A good answer provides an explanation in addition to working code. — Dale K, May 29 '22 at 08:41
Thank you for pointing it out and editing. Extracted '1' from '#1 OF 50 STUDENTS IN CLASS'. got 'OF 50 STUDENTS IN CLASS' from '#1 OF 50 STUDENTS IN CLASS', again got '50 STUDENTS IN CLASS' from 'OF 50 STUDENTS IN CLASS', and '50' is extracted from '50 STUDENTS IN CLASS'. Finally '1' and '50' is inserted into temp table inside loop. as per my friend's suggestion, I used temp table instead of table vriable considering the performance. All the inserting is done inside the loop, since we have to get the value from all the rows. Thank you. Please correct me if I am wrong. — Sanjay Ban, May 29 '22 at 16:01

RF1991 · Accepted Answer · 2022-05-29T06:11:30.937

your data

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

first you get rank and next of character by using SUBSTRING, CHARINDEX and REPLACE function

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

Id	RankDetails	Rank	details
1	#1 of 223 hotels in Maldives	1	of 223 hotels in Maldives
2	#3 of 223 hotels in Maldives	3	of 223 hotels in Maldives
3	#10 of 223 hotels in Maldives	10	of 223 hotels in Maldives
4	#126 of 223 hotels in Maldives	126	of 223 hotels in Maldives

then use Subquery to get values from details column

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

dbfiddle

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

5 Answers5