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)



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.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