Split string and Remove last value-CodePudding

I've to split string by using '/'.

Input:

INTC/MODB2/0241-07133/0010-81717/0010-36299

I have to loop through it and the expected output is like:

INTC/MODB2/0241-07133/0010-81717/0010-36299
INTC/MODB2/0241-07133/0010-81717
INTC/MODB2/0241-07133
INTC/MODB2
INTC

I tried using split function, but I'm able to print the exact output

SELECT value 
FROM STRING_SPLIT('INTC/MODB2/0241-07133/0010-81717/0010-36299', '/');

Appreciate any help

CodePudding user response：

Answer:

A recursive CTE is an option:

DECLARE @text varchar(1000) = 'INTC/MODB2/0241-07133/0010-81717/0010-36299'
DECLARE @separator varchar(1) = '/';

WITH rCTE AS(
    SELECT CHARINDEX(@separator, CONCAT(@text, @separator)) As Position
    UNION ALL
    SELECT CHARINDEX(@separator, CONCAT(@text, @separator), Position   1) AS Position
    FROM rCTE
    WHERE CHARINDEX(@separator, CONCAT(@text, @separator), Position   1) > 0
)
SELECT SUBSTRING(CONCAT(@text, @separator), 1, Position - 1) AS [Value]
FROM rCTE
ORDER BY Position DESC
OPTION (MAXRECURSION 0)

Result:

Value
-------------------------------------------
INTC/MODB2/0241-07133/0010-81717/0010-36299
INTC/MODB2/0241-07133/0010-81717
INTC/MODB2/0241-07133
INTC/MODB2
INTC

Update:

If the text is stored in a table, you need a different statement:

Table:

CREATE TABLE Data (Id int, Notes varchar(1000))
INSERT INTO Data 
VALUES 
   (1, 'INTC/MODB2/0241-07133/0010-81717/0010-36299'),
   (2, 'ABCD/0010-36299')

Statement:

DECLARE @separator varchar(1) = '/';
WITH rCTE AS(
    SELECT
       d.Id,
       d.Notes,
       CHARINDEX(@separator, CONCAT(d.Notes, @separator)) AS Position
    FROM Data d   
    UNION ALL
    SELECT
       r.Id,
       r.Notes,
       CHARINDEX(@separator, CONCAT(r.Notes, @separator), r.Position   1) AS Position
    FROM rCTE r
    WHERE CHARINDEX(@separator, CONCAT(r.Notes, @separator), r.Position   1) > 0
)
SELECT Id, SUBSTRING(CONCAT(Notes, @separator), 1, Position - 1) AS [Value]
FROM rCTE
ORDER BY Id, Position DESC
OPTION (MAXRECURSION 0)