Solved

T-SQL: ORDER BY(CASE WHEN?)

Posted on 2009-04-06
13
1,606 Views
Last Modified: 2012-05-06
Hi, T-SQL Experts,
Here is my question:
I am tring to use ORDER BY in SELECT statement to create an orderly list as following:
1.0.1
1.0.2
1.0.2.1.1.1
2.0
2.0.1
10.0
10.0.1.1
11.2.1
......
I know you can somehow using CASE WHEN statement to solve some simple re-order manipulation, but I don't know how I can use CASE WHEN to sort-out this particular problem(not a simple one, I guess).

Any idea?
-Rick
0
Comment
Question by:RHADMIN
  • 6
  • 5
  • 2
13 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24081613
The best, easiest way would be to pad out each digit to a fixed length and always include all digits.

ex:

1.0.1

would become:
0001.0000.0001.0000.0000.0000

then you can do alpha sorting.  What control do you have over that field?
0
 
LVL 1

Author Comment

by:RHADMIN
ID: 24081709
Thanks, Brandon, this is quick response!
the number "1.0.1" is entered freely(with RegExp Validation) by end-users into the db. I was thinking about using padding too, but a different way:

using CASE WHEN statement, strip out the all "." from the string, and add multiple "0" at the end, then ORDER BY with the modified/fixed-size string:
101000
102000
102111
200000
201000
100000
100110
112100
...
I just don't know how to modified these string in CASE WHEN, can you help?
Thanks,
-Rick

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24082706
So what values can exist?  Is each component no more than 2 digits?  3?  4?  Also, is this SQL 2005 or higher?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24082712
Also, will it always be no more than 6 components?
0
 
LVL 1

Author Comment

by:RHADMIN
ID: 24082775
Hi, Brandon,
I was wrong about my method of padding after having a closer look, it will not work :(
Your method of seperating each compnent is correct instead, each compnent should has no more than 4 digits, I am using SQL 2005.

Thanks,
-Rick
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 24082834
Ok.  What is the maximum number of components each will have?

This will work with up to 8 sections with 5 numeric positions in each.

Now, don't get confused into thinking this is a good thing to do.  This will cause EVERY record in your result set to be scanned, converted and then sorted.  It will be slow at best.  But it will work.

The below is posted with a data sample as well.
drop function dbo.ParseJunk 

go

create function dbo.ParseJunk (@JunkIn nvarchar(255))

returns varchar(255)

as

begin

declare @junkOut nvarchar(255)

     ,@delimiter nchar(1)

set @delimiter = N'.'
 

;with nm as (select 1 as n 

union all select 2

union all select 3

union all select 4

union all select 5

union all select 6

union all select 7

union all select 8

union all select 9

union all select 10

union all select 11

union all select 12

union all select 13

union all select 14

union all select 15

union all select 16

union all select 17

union all select 18

union all select 19

), f as 

(select n,right('00000'+substring(@delimiter + @JunkIn + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @JunkIn + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2),5) as string_value

from nm

where n <= (datalength(@delimiter + @JunkIn + @delimiter)/2) - (datalength(@delimiter)/2)

			and substring(@delimiter + @JunkIn + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 

)

select @junkOut = cast(

(select string_value+'' from f order by n for xml path('') )

 as varchar(255))+'0000000000000000000000000000000000000000'

return left(@Junkout,40)

end

go
 
 
 

select a,dbo.ParseJunk (a)

from 

(select '1.0.1' a

union select '1.0.2'

union select '1.0.2.1.1.1'

union select '2.0'

union select '2.0.1'

union select '10.0'

union select '10.0.1.1'

union select '11.2.1')b

order by dbo.ParseJunk (a)

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24082845
That function is a specialized for you modification of the fn_DelimitedToTable function I have posted over at my site SQL Server Nation.

http://sqlservernation.com/blogs/howtos/archive/2009/03/07/converting-a-delimited-string-into-a-table.aspx
0
 
LVL 1

Author Comment

by:RHADMIN
ID: 24082923
Thanks Brandon, I'd have to digest it a bit and tested it by tomorrow morning(time to go home), back to you then.
Thank you again for your instantaneous, generous & professional help!
-Rick
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 24101451
Always a problem when you have numbers stuck in a string it will do an alphabetic sort and not a numeric sort.

You can break apart based on the period as the delimiter, but your other challenge is defining the same number of columns across all strings.

In that regard, might be worth considering a scaler function. The advantage is any "where" clauses can immediately reduce the rowset we need to deal with, also, it will only work as hard as there are components in the string.

We have artificially set the max string length to 60 but could easily be varchar(max) if you want and essentially limitless number of components. Similarly length is set to a 5 digit number for each component.

Have a look below...
-- step 1 create a function to format our field - we will call it f1
 

create function uformat_f1(@f1 varchar(60))

returns varchar(60)

as

begin
 

   if charindex('.',@f1) = 0 return replace(@f1,'-','.')

   set @f1=rtrim(@f1)

   if charindex('-',@f1) = 0 set @f1=@f1+'-'

   

   declare @pos int

   declare @len int

   declare @mid int

   declare @end int

   declare @new varchar(60)

   declare @rhs varchar(60)

   declare @mds varchar(60)

   declare @lhs varchar(60)
 

   set @len = len(@f1)

   set @mid = charindex('.',reverse(@f1))

   set @end = charindex('-',@f1)

   set @pos = @len - @mid
 

   set @rhs = substring(@f1,@end,50)

   set @lhs = left(@f1,@pos)

   if charindex('.',@lhs) = 0 set @lhs = right('00000'+@lhs,5)

   set @mds = substring(@f1,@pos+2,@end - @pos - 2)

   set @new = @lhs+'-'+right('00000'+@mds,5)+@rhs
 

   return dbo.uformat_f1(@new)
 

end

go
 

-- step 2 create a test table with the test data provided above
 

create table #temp1(f1 varchar(50))

insert into #temp1 values ('1.0.1')

insert into #temp1 values ('1.0.2')

insert into #temp1 values ('1.0.2.1.1.1')

insert into #temp1 values ('2.0')

insert into #temp1 values ('2.0.1')

insert into #temp1 values ('10.0')

insert into #temp1 values ('10.0.1.1')

insert into #temp1 values ('11.2.1')
 

-- step 3 now we are ready to use it
 

select dbo.uformat_f1(f1), f1 from #temp1

order by 1

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24101529
in comparing your function to mine MW, I guess It wasn't necessary to right pad the 0s out.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24101614
Correct, number might not have enough positions, so assumed we would have to stick with alphabetic sorts, in which case rhs becomes redundant.
Also, do not have to define a number of columns as such, can simply build the string
Also, do an order by <column number> rather than use the udf again.
0
 
LVL 1

Author Comment

by:RHADMIN
ID: 24102549
It work! Thank you both! I learned a lots from your scripts(it really takes me some time to understand the logic(string manipulation)): CTE, XML PATH, RECURSION, all good stuffs in single example. LOVE THEM! Just realize that SQL can really do powerful processing which I originally think very difficult(if not possible) - provided you have a good imagination!
Again, thank you both for your professional help!
-Rick

0
 
LVL 1

Author Closing Comment

by:RHADMIN
ID: 31567232
Excellent scripts!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now