• # AlphaNumeric with hyphen sorting

Please find my ID here. I would like to sort this ID either in SQL or in Crystal Report formula. Thank You

ID

P1
P2
P3
P4-1
P4-2
P4-3
P5
P6
P7-a
P7-b
P7-c
P8
P9
.
.
.
P20
• Refer the below scrip

create table #test (id nvarchar(25))

select * from #test

insert into #test
select 'P1' union all
select 'P2' union all
select 'P3' union all
select 'P4-1' union all
select 'P4-2' union all
select 'P4-3' union all
select 'P5' union all
select 'P6' union all
select 'P7-a' union all
select 'P7-b' union all
select 'P7-c' union all
select 'P8' union all
select 'P9' union all
select 'P20'

select * from #test
order by cast(SUBSTRING(id, 1,
case when patindex('%[^0-9]%',id) > 0 then patindex('%[^0-9]%',id)- 1 else LEN(id) end) as int), id

Select id From #test
ORDER BY
Case When IsNumeric(id) = 1 then Right(Replicate('0',21) + id, 20)
When IsNumeric(id) = 0 then Left(id + Replicate('',21), 20)
Else id
End

Regards
Siva

• Hi Siva,

Thank you for your reply. When I execute the script it came as below

P1
P2
P20
P3
P4

and so on..

P20 supposed to come at last. How can I do that?

• Hi

By using this function we can sort AlphaNumeric data.

`create FUNCTION [dbo].[AlphaNum]( @input varchar(max))RETURNS varchar(max)ASBEGIN declare @num varchar(50) declare @space varchar(50) declare @index int = 1 set @num = LEFT(SUBSTRING(@input, PATINDEX('%[0-9.-]%', @input), 8000), PATINDEX('%[^0-9.-]%' ,SUBSTRING(@input, PATINDEX('%[0-9.-]%', @input), 8000) + 'X')-1) set @space = replicate(' ', 20 - len(@num)) return replace(@input, @num, @space + @num)END`

• Thank you all. I tried everything, but still not working. Could you please send an other option.

Thank you