SQL Server : String convert Numeric and sorting issue

Question

When I select there is one column to show.

And I have a below example codes

C*12:03:01:01
C*12:03:01:02
C*12:03:06
C*12:109
C*12:110
C*12:111
C*12:23

It should be there is : delimiter.

And when it's sorting it should sort each level

For example first level 12 it's all same so same sorting and second level 03, 03, 23, 109, 110,111 like number and then third ...

So it should show as below

C*12:03:01:01
C*12:03:01:02
C*12:03:06
C*12:23
C*12:109
C*12:110
C*12:111

I have tried delete all character and show only number with below function

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

RETURN @String

END

When I try

select dbo.fn_StripCharacters(DATA.AlleleName, '^0-9') 
from @temp
where CAST(dbo.fn_StripCharacters(DATA.AlleleName, '^0-9') AS INT)

Then It will show

1223
12109
12110
12111
120306
12030101
12030102

So I change my mind each level (Delimited by : ) has only 3 character maximum.

Do you guys know way to make

012:003:001:001
012:003:001:002
012:003:006:000
012:109:000:000
012:110:000:000
012:111:000:000
012:023:000:000

Then If I remove : and sorting maybe it will work.

Do you guys have any better idea for this? Thank you!

Answer 1

In your example, I think you can have parsename() do the work for you:

order by parsename(replace(col, ':', '.'), 4),
         cast(parsename(replace(col, ':', '.'), 3) as int),
         cast(parsename(replace(col, ':', '.'), 2) as int),
         cast(parsename(replace(col, ':', '.'), 1) as int)

Your examples all have four parts, so this should work. Also, the first element looks (from your examples) like it can be sorted alphabetically.

Answer 2

You can write a function that will return a part of a string splitted by a certain delimiter at a given position. Example:

alter function [dbo].[f_Split]
    (
        @List nvarchar(max),
        @SplitOn varchar(5),
        @Position int
    )  
    RETURNS nvarchar(250)
    AS  
    BEGIN 
        declare @Index int = 0
        declare @Value nvarchar(250) = ''

        while (Charindex(@SplitOn, @List) > 0) and @Index != @Position
            begin
                set @Value = ltrim(rtrim(substring(@List,1,charindex(@SplitOn,@List)-1)))
                set @List = substring(@List,charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

                set @Position = @Position - 1
            end

        if @Index != @Position
            set @Value = ''

        return @Value
    END

GO

Then you can use it in your query like this:

declare @temp table (col varchar(50))

insert into @temp (col) values ('C*12:03:01:01')
insert into @temp (col) values ('C*12:03:01:02')
insert into @temp (col) values ('C*12:03:06')
insert into @temp (col) values ('C*12:23')
insert into @temp (col) values ('C*12:109')
insert into @temp (col) values ('C*12:110')
insert into @temp (col) values ('C*12:111')

select 
    *
from 
    @temp 
order by
    dbo.f_split('C*12:03:01:01', ':', 5),
    dbo.f_split('C*12:03:01:01', ':', 4),
    dbo.f_split('C*12:03:01:01', ':', 3),
    dbo.f_split('C*12:03:01:01', ':', 2),
    dbo.f_split('C*12:03:01:01', ':', 1)