SQL Server String Data Sorting

Question

I have a scenario in which I am pulling some strings on the bases of some search text.

All things are working fine but the data display order(sorting) is not helping much of it.

I am attaching a script here which will illustrate the scenario.

SELECT  *
INTO    #Temp
FROM    ( SELECT    1 AS ID ,
                'Strawberry + Pineapple YZ Topper ' AS Name
      UNION ALL
      SELECT    2 AS ID ,
                'Strawberry + White Crisp '
      UNION ALL
      SELECT    3 AS ID ,
                'Strawberry Orange Froyo '
      UNION ALL
      SELECT    4 AS ID ,
                'Strawberry, Apple, Banana  Btb'
      UNION ALL
      SELECT    5 AS ID ,
                'Strawberry, Pineapple Banana '
      UNION ALL
      SELECT    6 AS ID ,
                'Strawberry, Pineapple, Kiwi Trt'
      UNION ALL
      SELECT    7 AS ID ,
                '2018 Delicious  with  Strawberries Pineapple'
      UNION ALL
      SELECT    8 AS ID ,
                'Apple  Devi Strawberries'
      UNION ALL
      SELECT    9 AS ID ,
                'Be Happy-Tissue Dipped Strawberries'
    ) temp1

  SELECT  ROW_NUMBER() OVER ( ORDER BY ID ) AS [Row #] ,
    #Temp.ID AS [Current Order] ,
    Name
 FROM    #Temp
--ORDER BY #Temp.Name asc

 SELECT  ROW_NUMBER() OVER ( ORDER BY ID ) AS [Row #] ,
    #Temp.ID AS [Current Order] ,
    Name ,
    CASE WHEN ID = 4 THEN 1
         WHEN ID = 3 THEN 2
         WHEN ID = 5 THEN 3
         WHEN ID = 6 THEN 4
         WHEN ID = 1 THEN 5
         WHEN ID = 2 THEN 6
         WHEN ID = 7 THEN 7
         WHEN ID = 8 THEN 8
         WHEN ID = 9 THEN 9
    END AS [Required Order]
 FROM    #Temp
 ORDER BY CASE WHEN ID = 4 THEN 1
          WHEN ID = 3 THEN 2
          WHEN ID = 5 THEN 3
          WHEN ID = 6 THEN 4
          WHEN ID = 1 THEN 5
          WHEN ID = 2 THEN 6
          WHEN ID = 7 THEN 7
          WHEN ID = 8 THEN 8
          WHEN ID = 9 THEN 9
     END 

  DROP TABLE    #Temp

while executing the above query will results in two different tables, table one is the actual table which is the outcome of my search result and I have demonstrated in table 2 what I am expecting .

Any good suggestion will be appreciated.

Is there some underlying logic to the desired sort order, or is this just an arbitrary order you require for this exact set of values? — 3N1GM4, Oct 24 '18 at 13:37
I don't understand what you mean here. `[Row #]` and `[Current Order]` have the same value in the output of the `SELECT` statement. — Thom A, Oct 24 '18 at 13:38
Expecting "Strawberry Orange" to be sorted in between two values that start with "Strawbery, " (you want ID4, then ID3, then ID 5 - ID 3 has no comma after the Strawberry) isn't possible without some enforced sort logic. It's like asking for the numbers one to nine to be sorted as 1,2,5,4,9,8,3,7,6. You'd be better of adding a sort order column on the inserts. — Dave Brown, Oct 24 '18 at 13:45
The sorting doesn't make sense at all - it's neither alphabetic nor numeric... so as mentioned by Dave Brown, you will have to enforce the sorting logic. Maybe - if it's always the same logic behind the sorting - you might want to create a table with the corresponding texts and the sorting order and then join it to your query. This way you can use the stored sorting order like a template on all your queries. — Tyron78, Oct 24 '18 at 13:48
@TabAlleman , when You execute query you will see two different result sets, first is the result which I am getting now and second is the result set which I am expecting to , So by force I set the sequence so people may have an idea what is the requirement. — DareDevil, Oct 24 '18 at 14:25
Still unclear. I get that you want ID 4 to be 1st in the order. I am asking WHY. What is the logic behind the sorting that you want? If you just arbitrarily want ID4 to be first, then you already have the solution in your question, so what are you asking? — Tab Alleman, Oct 24 '18 at 14:33
@TabAlleman just because the entry four string having text which should come at first record, and hence forth. — DareDevil, Oct 24 '18 at 15:20
Still unclear! "should come at first record" based on WHAT? Why should entry four string come first? — Tab Alleman, Oct 24 '18 at 15:25
@TabAlleman Take a peek at my second answer. I think this is what OP was looking for. — John Cappelletti, Oct 26 '18 at 11:43

score 1 · Answer 1 · answered Oct 24 '18 at 13:44

If I understand your logic correctly, why not just do this:

SELECT  *
INTO    #Temp
FROM    ( SELECT    1 AS ID , 5 as OrderNumber,
                'Strawberry + Pineapple YZ Topper ' AS Name
      UNION ALL
      SELECT    2 AS ID , 6 as OrderNumber,
                'Strawberry + White Crisp '
      UNION ALL
      SELECT    3 AS ID , 2 as OrderNumber,
                'Strawberry Orange Froyo '
      UNION ALL
      SELECT    4 AS ID , 1 as OrderNumber,
                'Strawberry, Apple, Banana  Btb'
      UNION ALL
      SELECT    5 AS ID , 3 as OrderNumber,
                'Strawberry, Pineapple Banana '
      UNION ALL
      SELECT    6 AS ID , 4 as OrderNumber,
                'Strawberry, Pineapple, Kiwi Trt'
      UNION ALL
      SELECT    7 AS ID , 7 as OrderNumber,
                '2018 Delicious  with  Strawberries Pineapple'
      UNION ALL
      SELECT    8 AS ID , 8 as OrderNumber,
                'Apple  Devi Strawberries'
      UNION ALL
      SELECT    9 AS ID , 9 as OrderNumber,
                'Be Happy-Tissue Dipped Strawberries'
    ) temp1

  SELECT  
    Name
 FROM    #Temp 
 ORDER BY OrderNumber

It looks like you're hard coding the order anyway so why bother with the ROW_NUMBER() and complicated stuff

The Above Query is the result of the Data and I had to show that the data comes in this format, ignore [Row #], Assume I have 9 rows with a string data and have to sort that it will look like in table two. rest of the two columns are just supporting columns to show the current order and desired order. — DareDevil, Oct 24 '18 at 14:00

John Cappelletti · Accepted Answer · 2018-10-26T12:20:24.607

Upon a second read of the question and comments. I now suspect you are looking to sort based on a key word... in this case Strawberry as the primary and then alpha.

CROSS APPLY B will "clean" the string. Removing special characters and repeating spaces.

CROSS APPLY C will parse the clean string into columns using [space] as the delimiter.

Example

SELECT  A.*
       --,B.*
       --,C.*
  FROM  #Temp A
  CROSS APPLY (values ( replace(replace(replace(replace(Name,',',' '),'+',' '),'   ',' '),'  ',' ')  ) ) B(S)
  CROSS APPLY (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(S,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
              ) C
  Order By sum(1) over (Partition By Pos1) Desc ,Pos1 ,Pos2 ,Pos3

Returns

ID  Name                                
4   Strawberry, Apple, Banana  Btb      
3   Strawberry Orange Froyo             
5   Strawberry, Pineapple Banana        
6   Strawberry, Pineapple, Kiwi Trt     
1   Strawberry + Pineapple YZ Topper    
2   Strawberry + White Crisp            
7   2018 Delicious  with  Strawberries Pineapple
8   Apple  Devi Strawberries                    
9   Be Happy-Tissue Dipped Strawberries

Note:

CROSS APPLY C could be replaced with a table-valued function. Take a peek at How to extract values from column and update result in another column

did you try that code in fiddle or any editor? I have got your point but bit confused how to put apply with my temp table data. — DareDevil, Oct 26 '18 at 11:56
https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=f6b6cc57b07a95383a8cb75c21f868f9 — John Cappelletti, Oct 26 '18 at 11:58
Thank you John, That's correct. This is what I wanted to achieve and people are not getting what I wanted to ask. — DareDevil, Oct 26 '18 at 12:02

SQL Server String Data Sorting

2 Answers2