Order by with String values - SQL Server Forums

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)

Order by with String values

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-05-27 : 02:53:02

Hi all,
I have table with id column of nvarchar..

Now,WHEN I RUN THIS QUERY,

select ID from <TABLE>,

IT RETURNS


erfq-1
erfq-10
erfq-100
erfq-1000
erfq-10000
erfq-1001
erfq-101
erfq-11
erfq-12
erfq-19
erfq-2
erfq-3
erfq-30
erfq-4
erfq-5
erfq-50
erfq-6
erfq-9
erfq-99
erfq-999

WHERE AS I NEED IT AS BELOW

erfq-1
erfq-2
erfq-3
erfq-4
erfq-5
erfq-6
erfq-9
erfq-10
erfq-11
erfq-12
erfq-19
erfq-30
erfq-50
erfq-99
erfq-100
erfq-101
erfq-999
erfq-1000
erfq-1001
erfq-10000

How Can I achive this one ?

Thanks in Advance
Dana

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-27 : 03:08:44

DECLARE @TEST TABLE ( ID NVARCHAR(4000))
INSERT INTO @TEST
SELECT 'erfq-1' UNION ALL
SELECT 'erfq-10' UNION ALL
SELECT 'erfq-100' UNION ALL
SELECT 'erfq-1000' UNION ALL
SELECT 'erfq-10000' UNION ALL
SELECT 'erfq-1001' UNION ALL
SELECT 'erfq-101' UNION ALL
SELECT 'erfq-11' UNION ALL
SELECT 'erfq-12' UNION ALL
SELECT 'erfq-19' UNION ALL
SELECT 'erfq-2' UNION ALL
SELECT 'erfq-3' UNION ALL
SELECT 'erfq-30' UNION ALL
SELECT 'erfq-4' UNION ALL
SELECT 'erfq-5' UNION ALL
SELECT 'erfq-50' UNION ALL
SELECT 'erfq-6' UNION ALL
SELECT 'erfq-9' UNION ALL
SELECT 'erfq-99' UNION ALL
SELECT 'erfq-999'
SELECT * FROM @TEST ORDER BY CAST ( SUBSTRING(ID,6,LEN(ID)) AS INT)

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-05-27 : 03:10:41

Thank you very Much :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 03:14:29

SELECT ID FROM @Test ORDER BY LEN(ID), ID

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-27 : 03:14:38

quote:
Originally posted by raky

DECLARE @TEST TABLE ( ID NVARCHAR(4000))
INSERT INTO @TEST
SELECT 'erfq-1' UNION ALL
SELECT 'erfq-10' UNION ALL
SELECT 'erfq-100' UNION ALL
SELECT 'erfq-1000' UNION ALL
SELECT 'erfq-10000' UNION ALL
SELECT 'erfq-1001' UNION ALL
SELECT 'erfq-101' UNION ALL
SELECT 'erfq-11' UNION ALL
SELECT 'erfq-12' UNION ALL
SELECT 'erfq-19' UNION ALL
SELECT 'erfq-2' UNION ALL
SELECT 'erfq-3' UNION ALL
SELECT 'erfq-30' UNION ALL
SELECT 'erfq-4' UNION ALL
SELECT 'erfq-5' UNION ALL
SELECT 'erfq-50' UNION ALL
SELECT 'erfq-6' UNION ALL
SELECT 'erfq-9' UNION ALL
SELECT 'erfq-99' UNION ALL
SELECT 'erfq-999'
SELECT * FROM @TEST ORDER BY CAST ( SUBSTRING(ID,6,LEN(ID)) AS INT)