SQL splitfunction reverse

Question

I am trying to make an SQL splitfunction which works reverse. Means Input: 'Test1,Test2,Test3,Test4'

And the output should be:

|  ID  |  Value  | 

|  1   |  Test4  | 
|  2   |  Test3  |
|  3   |  Test2  |
|  4   |  Test1  |

I found a forwardworking function but I don't know what to change to make it work reversed. I have tried some stuff but it doesn't work.

Here is the original

CREATE FUNCTION [dbo].[SplitString]
(
 @InputString VARCHAR(8000),
 @Delimiter CHAR(1) 
)
RETURNS TABLE
AS
RETURN
(
 WITH Split(StartPos,Endpos)
 AS(
 SELECT 0 AS StartPos, CHARINDEX(@Delimiter,@InputString) AS Endpos
 UNION ALL
 SELECT Endpos+1, CHARINDEX(@Delimiter,@InputString,Endpos+1)
 FROM Split
 WHERE Endpos > 0
 )
 SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
 'Value' = SUBSTRING(@InputString,StartPos
           ,COALESCE(NULLIF(Endpos,0)
           ,LEN(@InputString)+1)-StartPos)
 FROM Split
)
GO

with

SELECT ID, Value 
FROM dbo.SplitString('Test1,Test2,Test3,Test4', ',');

you will get the output.

Are you using MySQL, MS SQL Server or Sybase? Don't tag products not involved. — jarlh
– jarlh, Commented Apr 6, 2017 at 15:04
if you split 'Test1,Test2,Test3,Test4' normaly you get Test1 Test2 Test3 Test4 and I need Test4 Test3 Test2 Test1 So that the first entry has the last ID — Christian Frei
– Christian Frei, Commented Apr 6, 2017 at 15:07
That will not help. If I order it desc it will order the view, but not the ID. Nothing will change Test1 would have ID 1. I want to give Test1 the last ID and Test4 the frist ID. — Christian Frei
– Christian Frei, Commented Apr 6, 2017 at 15:13

John Cappelletti · Accepted Answer · 2017-04-06 15:11:29Z

1

Select * from [dbo].[udf-Str-Parse-8K-Rev]('Test1,Test2,Test3,Test4',',')

Returns

RetSeq  RetVal
1       Test4
2       Test3
3       Test2
4       Test1

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-8K-Rev] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N Desc)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Select * from [dbo].[udf-Str-Parse-8K-Rev]('Dog,Cat,House,Car',',')

Notice the Order By in the Final Select

answered Apr 6, 2017 at 15:11

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Christian Frei Over a year ago

Wow great. Thank you

John Cappelletti Over a year ago

@ChristianFrei You may find this approach to be more performant that the recursive cte

Bernd Buffen · Accepted Answer · 2017-04-06 15:20:38Z

you can it also do it with a query like this:

SELECT
    cnt.n AS ID
    , SUBSTRING_INDEX(SUBSTRING_INDEX('Test1,Test2,Test3,Test4', ',', cnt.n),',',-1) AS `VALUE` FROM
    ( SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) cnt
WHERE 
    cnt.n <= CHARACTER_LENGTH('Test1,Test2,Test3,Test4') 
    - CHARACTER_LENGTH(REPLACE('Test1,Test2,Test3,Test4',',',''))+1;

sample

MariaDB [(none)]> SELECT
    ->     cnt.n AS ID
    ->     , SUBSTRING_INDEX(SUBSTRING_INDEX('Test1,Test2,Test3,Test4', ',', cnt.n),',',-1) AS `VALUE` FROM
    ->     ( SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ->     ) cnt
    -> WHERE
    ->     cnt.n <= CHARACTER_LENGTH('Test1,Test2,Test3,Test4')
    ->     - CHARACTER_LENGTH(REPLACE('Test1,Test2,Test3,Test4',',',''))+1;
+----+-------+
| ID | VALUE |
+----+-------+
|  1 | Test1 |
|  2 | Test2 |
|  3 | Test3 |
|  4 | Test4 |
+----+-------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

Collectives™ on Stack Overflow

SQL splitfunction reverse

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related