SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Sort Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hdv212
Posting Yak Master

Iran
140 Posts

Posted - 08/11/2008 :  13:14:32  Show Profile  Send hdv212 a Yahoo! Message  Reply with Quote
hi
in my table, i have a column with nvachar(20) but enter numeric value to it (1,2,3, ...).
my problem is that when i select from my table and sort with that column, it it does not sort correctly, it sorted like this :

Field1   Field2   Field3(nvarchar(20))
--------------------------------------
a1       b1       1
a2       b2       10
a3       b3       11
a4       b4       12

...

how to solve my problem and correct this problem ?
i want this result :

Field1   Field2   Field3(nvarchar(20))
--------------------------------------
a1       b1       1
a2       b2       2
a3       b3       3
a4       b4       4
...

thanks


tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 08/11/2008 :  13:20:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
Change the data type to int for field3.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/11/2008 :  13:37:11  Show Profile  Reply with Quote
Cant really understand why you've column as varchar if you're storing only numeric value.
Go to Top of Page

hdv212
Posting Yak Master

Iran
140 Posts

Posted - 08/11/2008 :  13:53:26  Show Profile  Send hdv212 a Yahoo! Message  Reply with Quote
thanks for reply
but i don't want to change it to int, because in the future, maybe user enter numeric+alphabetic values like this :
m/123/a54

now, how to solve sorting problem ?
thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/11/2008 :  14:06:00  Show Profile  Reply with Quote
quote:
Originally posted by hdv212

thanks for reply
but i don't want to change it to int, because in the future, maybe user enter numeric+alphabetic values like this :
m/123/a54

now, how to solve sorting problem ?
thanks


ok. dont change it to int but just cast it for the timebeing to get your sorting work correctly

SELECT *
FROM YourTable
ORDER BY CAST(Field3 AS int)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/11/2008 :  14:09:04  Show Profile  Reply with Quote
Please note that it will still break if you happen to have a non numeric data in your Field3. try this also and see if this works.This is not using any type of casting

SELECT *
FROM YourTable
ORDER BY LEN(Field3),Field3

Edited by - visakh16 on 08/11/2008 14:09:58
Go to Top of Page

hdv212
Posting Yak Master

Iran
140 Posts

Posted - 08/11/2008 :  14:25:54  Show Profile  Send hdv212 a Yahoo! Message  Reply with Quote
Thanks visakh16
nonce it works.
thanks again
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/12/2008 :  03:07:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000