SQL Sort Numeric Strings After Split

Question

I currently have char values in a table column which are in the format "IS-" and then 1 to 5 numbers, a possible period with either 2 numbers or a letter following the period.

Examples are, IS-1, IS-12, IS-123, IS-123.11, IS-123.a.

I need to split the string so that I grab only the number part, sort the strings ASC, and the bring the strings back together the way they were.

Explanation. I have the following set of values, IS-1170, IS-1171, IS-1172, IS-1173, IS-1174, IS-870.a, IS-871.a, IS-872.a. As you can see, because IS-1 comes before IS-8 they are sorting out of numerical order.

Any idea where to begin? I was thinking of using CASE, but I'm not really sure how to proceed.

Thanks.

What is your desired output? Do you want to exclude any values, or just have it laid out different? — , Aug 17 '17 at 17:06
You can look at https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string question. Function approach may help you. — Burcin, Aug 17 '17 at 17:16

Aaron Dietz · Accepted Answer · 2017-08-17T17:13:41.293

Do string functions in your ORDER BY to remove only the number. Something like this should work:

SELECT col
FROM table
ORDER BY CAST(CASE WHEN ISNUMERIC(SUBSTRING(col,4,20)) = 1
              THEN SUBSTRING(col,4,20)
              ELSE LEFT(SUBSTRING(col,4,20),CHARINDEX('.',SUBSTRING(col,4,20),0)-1)
         END AS NUMERIC)

This will first remove the IS- and check if the rest of the string is a number. If it is, it will leave the decimal digits, otherwise it will remove the . and the following alpha characters.

This is assuming your intended ordering in the case of numeric decimal places would be:

IS-123.A
IS-123.1
IS-123.2

If you don't care about what's after the decimal/period, then simply:

ORDER BY CAST(LEFT(SUBSTRING(col,4,20),CHARINDEX('.',SUBSTRING(col,4,20),0)-1) AS NUMERIC)

Das ist fantastisch:) – Javlon Ismatov Aug 17 '17 at 17:34 — Javlon Ismatov, Aug 17 '17 at 17:34
WOW! Thanks Aaron. Perfect. – mappingman Aug 17 '17 at 18:31 — mappingman, Aug 17 '17 at 18:31

score 0 · Answer 2 · answered Aug 17 '17 at 17:18

If I understand you correctly, this might help you:

DECLARE @mockup TABLE(ID INT IDENTITY,YourExample VARCHAR(100));
INSERT INTO @mockup VALUES
 ('IS-1, IS-12, IS-123, IS-123.11, IS-123.a.')
,('IS-1170, IS-1171, IS-1172, IS-1173, IS-1174, IS-870.a, IS-871.a, IS-872.a');

WITH Splitted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE(m.YourExample,',','</x><x>') + '</x>' AS XML) AS SplitAtComma
    FROM @mockup AS m
)
,NumberExtracted AS
(
    SELECT s.ID
          ,part.value('text()[1]','nvarchar(max)') AS OnePart
          ,CAST('<y>' + REPLACE(REPLACE(part.value('text()[1]','nvarchar(max)'),'.','-'),'-','</y><y>') + '</y>' AS XML).value('/y[2]/text()[1]','int') AS TheNumber 
    FROM Splitted AS s
    CROSS APPLY s.SplitAtComma.nodes('/x') AS A(part)
)
SELECT *
FROM NumberExtracted
ORDER BY ID,TheNumber;

The first CTE uses a string-split via XML to get all values within the original string (btw: never store comma separated values!). The second CTE will use the same approach to extract the number, typesafe as INT. You can use this in an ORDER BY finally.

The result:

+----+-----------+-----------+
| ID | OnePart   | TheNumber |
+----+-----------+-----------+
| 1  | IS-1      | 1         |
+----+-----------+-----------+
| 1  | IS-12     | 12        |
+----+-----------+-----------+
| 1  | IS-123    | 123       |
+----+-----------+-----------+
| 1  | IS-123.11 | 123       |
+----+-----------+-----------+
| 1  | IS-123.a. | 123       |
+----+-----------+-----------+
| 2  | IS-870.a  | 870       |
+----+-----------+-----------+
| 2  | IS-871.a  | 871       |
+----+-----------+-----------+
| 2  | IS-872.a  | 872       |
+----+-----------+-----------+
| 2  | IS-1170   | 1170      |
+----+-----------+-----------+
| 2  | IS-1171   | 1171      |
+----+-----------+-----------+
| 2  | IS-1172   | 1172      |
+----+-----------+-----------+
| 2  | IS-1173   | 1173      |
+----+-----------+-----------+
| 2  | IS-1174   | 1174      |
+----+-----------+-----------+

Hint: You'll have to use `LTRIM` and/or `RTRIM` to avoid leading or trailing blanks... — Shnugo, Aug 17 '17 at 17:21

score 0 · Answer 3 · answered Aug 17 '17 at 17:59

IF OBJECT_ID(N'tempdb..##table1', N'U') IS NOT NULL   
DROP TABLE ##table1;  
create table ##table1(col1 varchar(20))

declare @query as nvarchar(max)
declare @var1 as varchar(max)='IS-1, IS-12, IS-123, IS-123.11, IS-123.a.,IS-1170, IS-1171, IS-1172, IS-1173, IS-1174, IS-870.a, IS-871.a, IS-872.a.'
set @var1=replace(@var1,',','''),(''')
set @var1='('''+@var1+''')'
set @var1=replace(@var1,' ','')
set @query='insert into ##table1 values'+@var1
EXEC sp_executesql @query
IF OBJECT_ID(N'tempdb..##table2', N'U') IS NOT NULL   
DROP TABLE ##table2;  
select * into ##table2  from ##table1 order by cast(replace(replace(replace(col1,'IS-',''),'.a.',''),'.a','') as float)

declare @results varchar(max)
select @results = coalesce(@results + ', ', '') +  convert(varchar(12),col1) from ##table2
select @results

DROP TABLE ##table1 
DROP TABLE ##table2

SQL Sort Numeric Strings After Split

3 Answers3

Linked