sql query sorting

Hi All,
i have a customerid field, 1,2,3,20,30,31,32,100,150,250
but when we sort it comes 1,100,150,2,20,250,30,31,32

is there any way we can sort incremented numbers using sql query
romeiovasuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Is your field a character (char, varchar, nvarchar) or numeric (int, money, numeric)?
What you describe is typical of character sorting, so to sort numerically you can CAST(that number as numeric(19,4))

This also assumes that every value is a number, otherwise you'd have to weed them out.
0
romeiovasuAuthor Commented:
it has got character also inside
example c01, c02, c03, c20, C25
0
Paul MacDonaldDirector, Information SystemsCommented:
It sounds like your column is a varchar column, not an integer or decimal column.  If you can change the column type, you should be okay.  Otherwise you may have to CAST the column value before you sort on  it.

(Nevermind - I see [Jim Horn] beat me)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mike EghtebasDatabase and Application DeveloperCommented:
You can also use Format() in your order By clause.

Say, the maximum charters is 5,

Order By FORMAT(FieldName, ‘00000’)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>it has got character also inside
Just a thought:  That's relevant to your question, so details like this should be included in the question.

Is it always the left-most single character?  If yes, you can remove it..

SELECT value
FROM your_table
ORDER BY CAST(RIGHT(value, LEN(value) - 1)  as int)

Open in new window



Although you may have to deal with other issues such as blanks or NULL values, more than one letter, or other letters, based on if the data is as advertised here.
0
romeiovasuAuthor Commented:
i have values like this C0010001F
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>i have values like this C0010001F
Before we go further, instead of a 'multiple iterations of ten words or less' approach, how about providing us a data mockup of the data you're working with.
0
Paul MacDonaldDirector, Information SystemsCommented:
Obviously all those values can't mean the same thing.  Should they all be in the same column?
0
Scott PletcherSenior DBACommented:
You can do something like this:

ORDER BY RIGHT(REPLICATE('0', 9) + customerid, 9)

Adjust the "9" to be the max length you'd ever expect -- extra length won't give you bad results, it will just add a few more bytes to the sort column value.  For example:

ORDER BY RIGHT(REPLICATE('0', 12) + customerid, 12)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.