?
Solved

T-SQL: ORDER BY(CASE WHEN?)

Posted on 2009-04-06
13
Medium Priority
?
1,657 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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 1000 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
 
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 1000 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

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

777 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