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

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!

Gordon Linoff · Accepted Answer · 2014-11-14 14:58:37Z

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.

edited Nov 14, 2014 at 14:58

answered Nov 14, 2014 at 14:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@clear.choi . . . I updated the answer to be database-correct.

SmartDev · Accepted Answer · 2014-11-14 15:53:46Z

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)

Collectives™ on Stack Overflow

SQL Server : String convert Numeric and sorting issue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related