Order a string field made of numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Order a string field made of numbers

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-09-14 : 11:56:49

What I have is a field set to varchar that has process numbers in it from 1 tp 16 and then the subnumbers like so, what i need to do is order them properly so that they all fall in line. Heres a p[ortion of what i have gotten but can't seem to get the rest in line. How can i get these to line up so all number in line with subnumbers such as
1
1.1
1.1.1
1.2
1.2.1
1.2.1.1
So on

Select strProcessNum from tblProcess
ORDER BY CAST( LEFT(strProcessNum + '.', CHARINDEX('.', strProcessNum + '.') - 1) As INT),
CASE WHEN strProcessNum LIKE '%.%' THEN CAST(REVERSE(LEFT(REVERSE(strProcessNum), CHARINDEX('.', REVERSE(strProcessNum))-1)) AS INT) ELSE 0 END,

1
2
3
4
5
6
7
8
15
9
10
11
12
13
14
15.1
15.3
15.2
15.4
16
3.1
3.2
3.3
3.4
4.1
4.2
4.3
4.4
5.1
5.2
5.3
5.4
5.5
5.6
5.7
15.1.1
5.8
8.1
8.2
8.3
9.1
9.2
12.1
12.2
12.3
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
10.1.1
10.3.1
10.2.2
10.1.2
10.3.2
10.3.3
10.4
11.1
11.2
11.3
11.4
11.5
11.6
13.1.1
13.1.2
13.4
13.2
13.3
14.1
14.2
14.5
14.3
7.1
7.2
7.3
7.4
7.5
7.6
7.7
7.8
16.1
16.2
16.3
16.4
16.5
16.6
16.7
16.8
16.9
16.10
16.11
16.12
16.13
16.14
16.15
16.16
6.1
6.2
6.2.1
6.2.2
6.2.3
6.3
6.4
6.5
6.6
1.1
1.1.1
1.1.2
1.1.3
1.1.4
1.1.5
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
1.3.3
1.3.4
1.3.5
1.3.6
8.1.1
8.1.1.1
15.2.1.2
15.2.1.1
8.1.2
8.1.3

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-14 : 13:16:48

ORDER BY CONVERT(INT,REPLACE(strProcessNum,'.',''))

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:19:07

how long can subnumbers grow?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-14 : 13:20:56

Ignore mine, it doesn't work!

Sorry

jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:22:09

quote:
Originally posted by jimf

ORDER BY CONVERT(INT,REPLACE(strProcessNum,'.',''))

Jim

Everyday I learn something that somebody else already knew

will that work?

consider 15.3 and 1.5.4

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-14 : 13:29:16

Well, at least I spotted I had the wrong answer a minute before you did! I take my victories however small.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:41:44

here's a method


CREATE FUNCTION GetWeightedValue
(@StrVal varchar(100)
)
RETURNS Numeric(15,15)
AS
BEGIN
DECLARE @Ret Numeric(15,15),@Cnt int,@Val int

SET @Cnt=0,@Ret=0
WHILE @StrVal IS NOT NULL
BEGIN
SELECT @Val=LEFT(@StrVal,CASE WHEN CHARINDEX('.',@StrVal)>0 THEN CHARINDEX('.',@StrVal)-1 ELSE LEN(@StrVal) END),
@StrVal=CASE WHEN CHARINDEX('.',@StrVal)>0 THEN SUBSTRING(@StrVal,CHARINDEX('.',@StrVal)+1 ,LEN(@StrVal)) ELSE NULL END
SELECT @Ret =@Ret + @Val * POWER(10,@Cnt),
@Cnt=@Cnt-1
END

RETURN @Ret
END

then use it like

ORDER BY dbo.GetWeightedValue(Col)

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-09-14 : 14:07:06

Hi any way to do outside of the function. This code is actually part of a dropdownbox and used with ajax and I don't know much about it other than its not numbering correctly, supporting a program and code that's above my knowledge level

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-14 : 14:13:28

I can't think of an efficient way to support that sort given nature of the values - especially if there is an unlimited number of nested sub group levels allowed. If you can't get into the data creation side (meaning when the data is populated also populate a sorting column) then probably the best thing you can do is do the sort on the application side when you populate your combo-box.

Be One with the Optimizer
TG

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-09-14 : 14:49:13

Hi,
this code is kind of sorting but has some hiccups in it, yes they originally could only go 4 deep now they want to go to infinity which is casuing a lot of problems with he code especially ordering it properly.

Select strProcessNum from tblProcess
ORDER BY CAST( LEFT(strProcessNum + '.', CHARINDEX('.', strProcessNum + '.') - 1) As INT),

nukeawhale
Starting Member

14 Posts

Posted - 2009-09-14 : 19:17:42

Not sure of how many first level outlines there could be, but this should work up to 99 first level outlines.

SELECT strProcessNum from tblProcess
ORDER BY REPLACE(CASE WHEN LEN(strProcessNum = 1 OR charindex('.', StrProcessNum) = 2 THEN '0'+strProcessNum ELSE strProcessNum END, '.','.0')

To get to 999 first level outlines, just pad "00" for 1-9 first level outlines and "0" for 10-99, this way:

SELECT strProcessNum FROM tblProcess
ORDER BY REPLACE(CASE WHEN LEN(StrProcessNum) = 1 OR charindex('.', StrProcessNum) = 2 THEN '00'+StrProcessNum ELSE
(CASE WHEN LEN(StrProcessNum) = 2 OR charindex('.', StrProcessNum) = 3 THEN '0'+StrProcessNum ELSE
StrProcessNum END) END,'.','.0')

You probably get the point, but you can take it to 9999 first level outlines by paddin more zeroes, but any more nested CASE statements and my head starts a hurtin.

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-09-15 : 13:50:33

nukeawhale,

Wow that's way better than a method given to me on a different site that requires a function combined with a 20 lines of code. I think i will use this option. thanks for the kowledge and help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources