Sorting data having special characters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sorting data having special characters

Author

Topic

amitranjan
Starting Member

45 Posts

Posted - 2010-02-10 : 09:43:01

I have data in my table something like this <-3 <-2 <-1 0 1 2 3 >3 >4 11,12,6,9,-4,-6,-9, <-7 etc

so what i want is a query that will give me the result some thing like this

-9,<-7,-6,-4, <-3 <-2 <-1 0 1 2 3,>3,>4,6,9,11,12...

amit Ranjan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 09:51:20

[code]ORDER BY RTRIM(LTRIM(REPLACE(REPLACE(yourcol,'<',''),'>','')))*1,CASE WHEN CHARINDEX('>',yourcol)> 0 THEN 2 WHEN CHARINDEX('<',yourcol)> 0 THEN 0 ELSE 1 END[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-10 : 10:04:05

Isn't it just enough?

ORDER BY RTRIM(LTRIM(REPLACE(REPLACE(yourcol,'<',''),'>','')))*1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 10:46:18

quote:
Originally posted by madhivanan

Isn't it just enough?

ORDER BY RTRIM(LTRIM(REPLACE(REPLACE(yourcol,'<',''),'>','')))*1

Madhivanan

Failing to plan is Planning to fail

See the difference below



SELECT VAL
from
(
SELECT '-9' AS Val UNION ALL
SELECT '<-7' UNION ALL
SELECT '-7' UNION ALL
SELECT '-6' UNION ALL
SELECT '-4' UNION ALL
SELECT '<-3' UNION ALL
SELECT ' <-2 ' UNION ALL
SELECT ' <-1 ' UNION ALL
SELECT ' 0 ' UNION ALL
SELECT ' 1 ' UNION ALL
SELECT ' 2 ' UNION ALL
SELECT ' 3' UNION ALL
SELECT '>-3' UNION ALL
SELECT ' >3' UNION ALL
SELECT ' <3' UNION ALL
SELECT ' >4' UNION ALL
SELECT '<-6' UNION ALL
SELECT ' 6' UNION ALL
SELECT ' 9' UNION ALL
SELECT ' 11' UNION ALL
SELECT ' 12' 
)T
ORDER BY RTRIM(LTRIM(REPLACE(REPLACE(VAL,'<',''),'>','')))*1

output
---------------------------
-9
-7
<-7
<-6
-6
-4
>-3
<-3
 <-2 
 <-1 
 0 
 1 
 2 
 <3
 3
 >3
 >4
 6
 9
 11
 12


SELECT VAL
from
(
SELECT '-9' AS Val UNION ALL
SELECT '<-7' UNION ALL
SELECT '-7' UNION ALL
SELECT '-6' UNION ALL
SELECT '-4' UNION ALL
SELECT '<-3' UNION ALL
SELECT ' <-2 ' UNION ALL
SELECT ' <-1 ' UNION ALL
SELECT ' 0 ' UNION ALL
SELECT ' 1 ' UNION ALL
SELECT ' 2 ' UNION ALL
SELECT ' 3' UNION ALL
SELECT '>-3' UNION ALL
SELECT ' >3' UNION ALL
SELECT ' <3' UNION ALL
SELECT ' >4' UNION ALL
SELECT '<-6' UNION ALL
SELECT ' 6' UNION ALL
SELECT ' 9' UNION ALL
SELECT ' 11' UNION ALL
SELECT ' 12' 
)T
ORDER BY RTRIM(LTRIM(REPLACE(REPLACE(VAL,'<',''),'>','')))*1,CASE WHEN CHARINDEX('>',VAL)> 0 THEN 2 WHEN CHARINDEX('<',VAL)> 0 THEN 0 ELSE 1 END



Output
------------------------------
-9
<-7
-7
<-6
-6
-4
<-3
>-3
 <-2 
 <-1 
 0 
 1 
 2 
 <3
 3
 >3
 >4
 6
 9
 11
 12

------------------------------------------------------------------------------------------------------
SQL Server MVP

amitranjan
Starting Member

45 Posts

Posted - 2010-02-10 : 11:36:12

quote:

Originally posted by visakh16

quote:
Originally posted by madhivanan

Isn't it just enough?

ORDER BY RTRIM(LTRIM(REPLACE(REPLACE(yourcol,'<',''),'>','')))*1

Madhivanan

Failing to plan is Planning to fail

See the difference below



SELECT VAL
from
(
SELECT '-9' AS Val UNION ALL
SELECT '<-7' UNION ALL
SELECT '-7' UNION ALL
SELECT '-6' UNION ALL
SELECT '-4' UNION ALL
SELECT '<-3' UNION ALL
SELECT ' <-2 ' UNION ALL
SELECT ' <-1 ' UNION ALL
SELECT ' 0 ' UNION ALL
SELECT ' 1 ' UNION ALL
SELECT ' 2 ' UNION ALL
SELECT ' 3' UNION ALL
SELECT '>-3' UNION ALL
SELECT ' >3' UNION ALL
SELECT ' <3' UNION ALL
SELECT ' >4' UNION ALL
SELECT '<-6' UNION ALL
SELECT ' 6' UNION ALL
SELECT ' 9' UNION ALL
SELECT ' 11' UNION ALL
SELECT ' 12' 
)T
ORDER BY RTRIM(LTRIM(REPLACE(REPLACE(VAL,'<',''),'>','')))*1

output
---------------------------
-9
-7
<-7
<-6
-6
-4
>-3
<-3
 <-2 
 <-1 
 0 
 1 
 2 
 <3
 3
 >3
 >4
 6
 9
 11
 12


SELECT VAL
from
(
SELECT '-9' AS Val UNION ALL
SELECT '<-7' UNION ALL
SELECT '-7' UNION ALL
SELECT '-6' UNION ALL
SELECT '-4' UNION ALL
SELECT '<-3' UNION ALL
SELECT ' <-2 ' UNION ALL
SELECT ' <-1 ' UNION ALL
SELECT ' 0 ' UNION ALL
SELECT ' 1 ' UNION ALL
SELECT ' 2 ' UNION ALL
SELECT ' 3' UNION ALL
SELECT '>-3' UNION ALL
SELECT ' >3' UNION ALL
SELECT ' <3' UNION ALL
SELECT ' >4' UNION ALL
SELECT '<-6' UNION ALL
SELECT ' 6' UNION ALL
SELECT ' 9' UNION ALL
SELECT ' 11' UNION ALL
SELECT ' 12' 
)T
ORDER BY RTRIM(LTRIM(REPLACE(REPLACE(VAL,'<',''),'>','')))*1,CASE WHEN CHARINDEX('>',VAL)> 0 THEN 2 WHEN CHARINDEX('<',VAL)> 0 THEN 0 ELSE 1 END



Output
------------------------------
-9
<-7
-7
<-6
-6
-4
<-3
>-3
 <-2 
 <-1 
 0 
 1 
 2 
 <3
 3
 >3
 >4
 6
 9
 11
 12

------------------------------------------------------------------------------------------------------
SQL Server MVP

Thanks vishak , u rock, it worked as i want

amit Ranjan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 11:37:46

Great

You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-11 : 00:28:59

I thought it should be sorted based only on numbers and not sysmbols

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 00:35:26

quote:
Originally posted by madhivanan

I thought it should be sorted based only on numbers and not sysmbols

Madhivanan

Failing to plan is Planning to fail

But OP's sample output showed consideration to symbol as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources