How to sort the below data

SQL Server Administration
1

the data like this

A-001
A-025
A-100
B-025
B-125
C-67
C-682
C-77
D-1
D-10
D-100
D-2
D-20
D-200
D-236
D-1001
E-2536
G-3588
H-045

I WANT TO DATA LIKE THIS

A-001
A-025
A-100
B-025
B-125
C-67
C-77
C-682
D-1
D-2
D-10
D-20
D-100
D-200
D-236
D-1001
E-2536
G-3588
H-045
[/quote]

2

You need to split the string into a number. You can use the function SUBSTRING and CHARINDEX to split the string into a number. For example:

DROP TABLE IF EXISTS #TestTable;

SELECT 
'A-001' AS [Column01] INTO #TestTable
UNION SELECT 'A-025'
UNION SELECT 'C-77'
UNION SELECT 'A-100'
UNION SELECT 'B-025'
UNION SELECT 'B-125'
UNION SELECT 'C-67'
UNION SELECT 'C-682'


SELECT * FROM #TestTable

SELECT 
	[Column01],
	CHARINDEX('-',[Column01]) + 1 AS StartingPosition,
	LEN([Column01]) AS LengthOfString,
	LEFT([Column01],1) AS CharPart,
	CONVERT(INT,SUBSTRING([Column01],
			  CHARINDEX('-',[Column01]) + 1, /* Startingposition of Number */
			   LEN([Column01])-CHARINDEX('-',[Column01]) /* Number of Char left) */
			 )) AS NumberPart
	
FROM
#TestTable
ORDER BY 
	LEFT([Column01],1), /*CharPart*/
	CONVERT(INT,SUBSTRING([Column01], CHARINDEX('-',[Column01]) + 1,  LEN([Column01])-CHARINDEX('-',[Column01]))) /* NumberPart */

I don't know if if you have a lot of data or if you need a faster solution. This is the most simple solution to understand as far as I know.

1 Like
3

hi

hope this helps

create data script

drop table #Data
create table #Data(String varchar(100))

insert into #Data select 'A-001'
insert into #Data select 'A-025'
insert into #Data select 'A-100'
insert into #Data select 'B-025'
insert into #Data select 'B-125'
insert into #Data select 'C-67'
insert into #Data select 'C-682'
insert into #Data select 'C-77'
insert into #Data select 'D-1'
insert into #Data select 'D-10'
insert into #Data select 'D-100'
insert into #Data select 'D-2'
insert into #Data select 'D-20'
insert into #Data select 'D-200'
insert into #Data select 'D-236'
insert into #Data select 'D-1001'
insert into #Data select 'E-2536'
insert into #Data select 'G-3588'
insert into #Data select 'H-045'

; with cte as 
(
   select String 
         , LEFT(String, CHARINDEX('-', String)-1) As Part1
		 , CAST(RIGHT(String, LEN(String) - CHARINDEX('-', String)) as int) As Part2
    from #Data 
)
select String  from cte order by part1,part2

image
image223×681 5.25 KB

4

hi

hope this helps

another way to do this

select string  from #Data  
order by 
REPLACE(TRANSLATE(String, '1234567890-', '###########'),'#',''), 
cast(REPLACE(TRANSLATE(String, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ-', '###########################'),'#','') as int)

image
image223×681 5.25 KB

5 
SELECT col1 FROM YourTable 
ORDER BY LEFT(col1,1),TRY_CAST(STUFF(col1,1,2,'') AS INT);
3 Likes
6

string manipulation will always come to bite your ash.
if at any point the data has something different as delimiter things will blow up :bomb:

As an example what if there are no - in your data, though you have shown us data with dash only? What then?

D#1914
D--1914
D=1914
D-1914     -> has spaces after 1914

select vader , 
       TRY_CAST(item as int)  meow, 
	   LEFT(vader,1) jelly,
	   TRY_CAST(STUFF(vader,1,2,'') AS INT) as jam  
from (
	select 'A-001' as vader union
	select 'A-025' union
	select 'A-100' union
	select 'B-025' union
	select 'B-125' union
	select 'C-67' union
	select 'C-682' union
	select 'C-77' union
	select 'D-1' union
	select 'D-10' union
	select 'D-100' union
	select 'D-2' union
	select 'D-20' union
	select 'D-200' union
	select 'D-236' union
	select 'D-1001' union
	select 'E-2536' union
	select 'G-3588' union
	select 'H-045' union
	select 'H--2022' union
	select 'H-2222   '
) a 
cross apply DelimitedSplit8K(rtrim(ltrim(a.vader)),'-')
where ItemNumber = 2
ORDER BY LEFT(vader,1),TRY_CAST(STUFF(vader,1,2,'') AS INT);