SQL Server Performance Forum – Threads Archive

nvarchar sort order

i have column in a table with data type nvarchar but i store on it numbers
when i perform any select statment the sort for that numbersis not corect
pls help

Do like this select * from TableName order by cast(FieldName as int) But it is better to change the filed type varchar to int

Dinesh is right. Choose the appropriate data type. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

try this too:
i’ve noticed it from one of frank’s post… select * from TableName order by FieldName * 1

How about this?
Select columns from yourTable order by len(nvarcharField),nvarcharField
But as suggested use proper datatype
Madhivanan Failing to plan is Planning to fail

thanks to you all but my value for that column as this 011000123
010102222
100143255
030245662
234156A

Then it wont work
Do you think 234156A is number?
So, what is expected result from those sample data?
Madhivanan Failing to plan is Planning to fail

thanks to Madhivanan
i think i got what i want from your way
thanks

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] You should have started the post by giving the sample data. anyway you got the answer that’s it

<pre id="code"> Select columns from yourTable order by len(nvarcharField),nvarcharField </pre id="code"> Really nice trick, Madhivanan! Will definitely keep that in mind. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis <pre id="code"> Select columns from yourTable order by len(nvarcharField),nvarcharField </pre id="code"> Really nice trick, Madhivanan! Will definitely keep that in mind. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <hr height="1" noshade id="quote"></blockquote id="quote"> Thanks for the appreciation [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>