sorting data which is string like an int

Question

sorting data which is string like an int

mary_mormann

Newbie

Points: 2
More actions
June 11, 2008 at 9:09 am

#186681

I have records which contains id,name,MoveNumber which looks like below.
1, Fred, 10
2, Wilma, 10-1
3, Barney, 101-1
4, Pebbles, 10-2
5, Marsha, 10-7
6, Tom, 97
7, Bob, 97-3
If I order by MoveNumber, it is sorting it like a string like above. I would like to sort it so that it is numeric like below, is this possible?
10
10-1
10-2
10-7
97
97-3
101-1
thanks,
Mary

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 739661 More actions · Answer 1

You'd have to convert things to numerics,stripping out the hyphens,for sorting purposes. Don't change the data, but extract the numbers (using CAST(SUBSTRING(CHARINDEX())) functions to get this.

noeld SSC Guru Points: 96590 More actions · Answer 2

mary_mormann (6/11/2008)
I have records which contains id,name,MoveNumber which looks like below.
1, Fred, 10
2, Wilma, 10-1
3, Barney, 101-1
4, Pebbles, 10-2
5, Marsha, 10-7
6, Tom, 97
7, Bob, 97-3
If I order by MoveNumber, it is sorting it like a string like above. I would like to sort it so that it is numeric like below, is this possible?
10
10-1
10-2
10-7
97
97-3
101-1
thanks,
Mary

there may be more clever ways to accomplish your task. This is just one solution:

declare @t table ( id int,name varchar(20), MoveNumber varchar(20))

insert into @t (id,name,MoveNumber) values (1, 'Fred', '10')

insert into @t (id,name,MoveNumber) values (2, 'Wilma', '10-1')

insert into @t (id,name,MoveNumber) values (3, 'Barney', '101-1')

insert into @t (id,name,MoveNumber) values (4, 'Pebbles', '10-2')

insert into @t (id,name,MoveNumber) values (5, 'Marsha', '10-7')

insert into @t (id,name,MoveNumber) values (6, 'Tom', '97')

insert into @t (id,name,MoveNumber) values (7, 'Bob', '97-3')

select *

from @t

order by

(case when charindex('-',MoveNumber) = 0

then cast(MoveNumber as int)

else cast(Left(MoveNumber, charindex('-',MoveNumber)-1) as int) end),

(case when charindex('-',MoveNumber) = 0

then cast(0 as int)

else cast(Right(MoveNumber, charindex('-',reverse(MoveNumber))-1) as int) end)

* Noel

mary_mormann Newbie Points: 2 More actions · Answer 3

This is exactly what I was looking for!

Thanks for your help.

Mary

noeld SSC Guru Points: 96590 More actions · Answer 4

noeld

SSC Guru

Points: 96590

June 11, 2008 at 1:26 pm

#827488

happy to help 🙂

* Noel