Alpanumeric sort

Transact-SQL
1

I have the following data in a sql table :

Blokno
3
2
1
5-BOP
3-ANW
11
4-BLY
23

I want to sort it and have the following result :

Blokno
1
2
3
3-ANW
4-BLY
5-BOP
11
23

I have tries the following :
SELECT blokno
FROM Blokke
ORDER BY LEFT(blokno, PATINDEX('%[0-9]%', blokno)-1),
CONVERT(INT, SUBSTRING(blokno, PATINDEX('%[0-9]%', blokno), LEN(blokno)))

But am getting an error (My guess is that I am not accounting for the hyphen?)

Any help would be much appreciated.

Regards

2

will the alpha numeric values always have a hyphen? or could another character type other than hyphen manifest itself in future data?

3

always provide sample DML DDL. this might work?

create table #mightygedion(blokno varchar(50))

insert into #mightygedion
select 'Blokno' blokno union
select '3' union
select '2' union
select '1' union
select '5-BOP' union
select '3-ANW' union
select '11' union
select '4-BLY' union
select '23'


select blokno, 
       substring( reverse(blokno), PATINDEX('%-%',reverse(blokno)) + 1, len(blokno)),
	   case
	     when isnumeric( substring( reverse(blokno), PATINDEX('%-%',reverse(blokno)) + 1, len(blokno)) ) = 1 then 
			cast(substring( reverse(blokno), PATINDEX('%-%',reverse(blokno)) + 1, len(blokno)) as int)
		 else -9999
	   end as Sorter
  from #mightygedion
  order by Sorter

  drop table #mightygedion
1 Like
4 
SELECT *
FROM #mightygedion
CROSS APPLY (
    SELECT PATINDEX('%[^0-9]%', blokno + '.') - 1 AS blokno_first_nonnum
) AS assign_alias_names
ORDER BY CAST(LEFT(blokno, blokno_first_nonnum) AS int), 
    SUBSTRING(blokno, blokno_first_nonnum + 1, 50)
5

Hi yosiasz. Thank you for the reply. A hyphen will always be used.