SplitString SQL server

Question

The below Split function works well. But it changes the order of my input. Please help me to get desired output.

With respect to http://www.codeproject.com/Tips/85943/Split-function.

Input

SELECT * from dbo.fnStringSplitter('1,3,2', ',')

Expected Output

1
3
2

NOT

1
2
3

You pass 1,2,3 and you want 1,3,2. I guess you are changing the order not split string function — Pரதீப்
– Pரதீப், Commented Jan 29, 2016 at 16:48
Edited by Question. Please work and getback. It is reasonable Question only — jamir
– jamir, Commented Jan 29, 2016 at 16:51
That is actually the worst performing splitter you can find. A loop is horribly inefficient for this type of thing. Check out this article for much better splitters. sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange
– Sean Lange, Commented Jan 29, 2016 at 16:54

Pரதீப் · Accepted Answer · 2016-01-29 17:08:41Z

1

You need to use Order by to get the result in required Order try changing the Split string function like this

Try this way

CREATE FUNCTION dbo.Fnstringsplitter (@string    NVARCHAR(MAX),
                                      @delimiter VARCHAR(2))
RETURNS @result TABLE(
  id        INT IDENTITY(1, 1),
  split_val NVARCHAR(MAX))
  BEGIN
      DECLARE @start INT,
              @end   INT

      SELECT @start = 1,
             @end = Charindex(@delimiter, @string)

      WHILE @start < Len(@string) + 1
        BEGIN
            IF @end = 0
              SET @end = Len(@string) + 1

            INSERT INTO @result
                        (split_val)
            VALUES      (Substring(@string, @start, @end - @start))

            SET @start = @end + 1
            SET @end = Charindex(@delimiter, @string, @start)
        END

      RETURN
  END

Query:

SELECT split_val
FROM   dbo.Fnstringsplitter('1,3,2', ',')
ORDER  BY id

Note: This is not a efficient way to split the data. But reliable in your case

edited Jan 29, 2016 at 17:08

answered Jan 29, 2016 at 16:53

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

3 Comments

jamir Over a year ago

This seems good. I will test and mark it as answered. I need as function. you have hardcorded. Give it as function

Pரதீப் Over a year ago

@jamir - Updated check now

jamir Over a year ago

need one more help. check Question i have asked in stackoverflow.com/questions/35102631/…

David Rushton · Accepted Answer · 2016-01-29 16:54:33Z

0

Datasets within SQL are inherently unordered. When you require an explicit order you must explicitly request/create it. This can be done using the ORDER BY clause or via code in your front end.

answered Jan 29, 2016 at 16:54

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

Collectives™ on Stack Overflow

SplitString SQL server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related