Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server Sorting
Hello Everyone,
 
I want to sort few records which actually contains Duration but they are in nvarchar datatype.. For eg.
 
Select '33:14:14' as 'Duration'
UNION
Select '1:16:36' as 'Duration'
UNION
Select '0:3' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '02:12:39' as 'Duration'
 
I want to sort them.. Unfortunately I am getting results in above format (like 0:3 which is wrong practice) but still is there any way to do so ?
 

Thanks,
Posted 18-Dec-12 20:23pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
One solution is converting your data to seconds either on the fly or by using a user defined function.
Here is a SQL statement that converts your data to seconds on the fly :
 
SELECT * FROM (
SELECT Duration ,
CASE
WHEN CHARINDEX(':',Duration )=0 THEN
    CAST (Duration AS DECIMAL(10,2))
WHEN CHARINDEX(':',Duration, CHARINDEX(':',Duration)+1) = 0 THEN
   CAST(SUBSTRING(Duration, 1, CHARINDEX(':',Duration)-1) *60 AS DECIMAL(10,2))
     + CAST(SUBSTRING(Duration, CHARINDEX(':',Duration)+ 1 , LEN(Duration)) AS DECIMAL(10,2))
WHEN CHARINDEX(':',Duration, CHARINDEX(':',Duration)+1) >0  THEN
    CAST(SUBSTRING(Duration, 1, CHARINDEX(':',Duration)-1) *3600.0 AS DECIMAL(10,2))
    + CAST(SUBSTRING(Duration,CHARINDEX(':',Duration)+ 1 , CHARINDEX(':',Duration , CHARINDEX(':',Duration)+1 ) - CHARINDEX(':',Duration) -1)*60  AS DECIMAL(10,2))
    + CAST(SUBSTRING(Duration,  CHARINDEX(':',Duration , CHARINDEX(':',Duration)+1 ) +1  , LEN(Duration)  ) AS DECIMAL(10,2))
ELSE 'unknown format'  END AS seconds
from
(
    Select '33:14:14' as 'Duration'
    UNION
    Select '1:16:36' as 'Duration'
    UNION
    Select '0:3.1' as 'Duration'
    UNION
    Select '0:3' as 'Duration'
    UNION
    Select '0.03' as 'Duration'
    UNION
    Select '0:29' as 'Duration'
    UNION
    Select '0:31' as 'Duration'
    UNION
    Select '3:0' as 'Duration'
    UNION
    Select '0:29' as 'Duration'
    UNION
    Select '02:12:39' as 'Duration'
)
as a
) AS b
ORDER BY seconds
 

Good Luck.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Note: Numeric conversion is must in this case for sorting AND insert data in proper format
see below example...
select dbo.SetFormat(duration) from
(
    Select '33:14:14' as 'Duration'
    UNION
    Select '01:16:36' as 'Duration'
    UNION
    Select '0:3' as 'Duration'
    UNION
    Select '0:29' as 'Duration'
    UNION
    Select '02:12:39' as 'Duration'
)
as a
order by convert(decimal,replace( dbo.SetFormat(duration),':',''))
 
create function
Collapse | Copy Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
    DECLARE @Value varchar(100)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END
Create this function also
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select dbo.Setformat('3:1')
CREATE FUNCTION Setformat
(
	@a varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
	select @a=sum(convert(int,t)) from(select  replicate('0',2-Len(val)) + val + replicate('0',((3-Id)*2)) as t from [dbo].[ParseValues](@a,':')) as a
 
	return @a
END
GO

Happy Coding!
Smile | :)
  Permalink  
v6
Comments
Amir Mahfoozi at 19-Dec-12 1:55am
   
Have a look at the result of this query :
SELECT Duration , convert(numeric,(replace(duration,':',''))) from
(
Select '33:14:14' as 'Duration'
UNION
Select '1:16:36' as 'Duration'
UNION
Select '0:3' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '0:31' as 'Duration'
UNION
Select '3:0' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '02:12:39' as 'Duration'
)
as a
order by convert(numeric,(replace(duration,':','')))
Aarti Meswania at 19-Dec-12 1:59am
   
yes right can't use number
I have modified it thank you
Amir Mahfoozi at 19-Dec-12 2:01am
   
Also think about this case :
Select convert(datetime,'1900-01-01 33:12:39') as 'Duration'
Aarti Meswania at 19-Dec-12 2:03am
   
right
Aarti Meswania at 19-Dec-12 2:15am
   
now you can check this
 
data should be in fixed format e.g hh:mm:ss
 
then it is easy to do sorting on it
Amir Mahfoozi at 19-Dec-12 2:23am
   
According to this sample data type '33:14:14' in the question,
converting to datetime will always fail.
Amir Mahfoozi at 19-Dec-12 2:27am
   
The OP clearly says :
Unfortunately I am getting results in above format (like 0:3 which is wrong practice)
So your query will not work correctly for this type of input :
Select '0:31' as 'Duration'
UNION
Select '3:1' as 'Duration'
Aarti Meswania at 19-Dec-12 2:32am
   
yes in that case create need to vreate own function that convert that field value in to hh:mm:ss format first, then sorting will easy
Amir Mahfoozi at 19-Dec-12 2:36am
   
I don't need that function.
If OP reads your comments he should create his own function. ;)
Aarti Meswania at 19-Dec-12 2:44am
   
I have modified last comment it was mistake writing sentence

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100