?
Solved

T-SQL: ORDER BY(CASE WHEN?)

Posted on 2009-04-06
13
Medium Priority
?
1,664 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 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