sorting varchar data type column containing zero or multiple decimal point

Question

3.40/5 (3 votes)

See more:

Hello sql developers ,

I want to sort the number

1. It may contain zero or multiple decimal point e.g. 1 or 1.0.1

2. number may be like 00001 Note here we may trim 0000 to make it to 1

Column Data type is varchar(50)

eg.

1.
1.2
1.2.1
1.2.3
1.2.3.1
2.
00003 here we may trim 0000 to make it to 3
5.
7
100

the above one is just example of sorting that i want to do.

how shpuld i write query or function to do above sorting

Help is needed

Regards

Nitin

Posted 23-Dec-13 19:11pm

patil.nitin8358

Updated 23-Dec-13 19:56pm

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 24-Dec-13 1:26am

Numbers don't have decimal points; only string representing numbers may have them. Can you see the difference?
—SA

patil.nitin8358 24-Dec-13 1:37am

i already mentioned datatype as varchar so dont go on title

Sergey Alexandrovich Kryukov 24-Dec-13 1:40am

Let me go to any point I can see. You can change your title, for your own benefit.
—SA

patil.nitin8358 24-Dec-13 1:57am

now i have changed question title as per ur suggestion.

Christian Graus 24-Dec-13 1:58am

Not sure of the drama, your question was clear to at least two people and has been answered.

Sergey Alexandrovich Kryukov 24-Dec-13 2:23am

Yes it was, so what? Who was talking about drama? Accurate phrasing is always beneficial. By the way, even you did not quite understand my point: numbers don't have even one decimal point. They don't have any "points" at all, they are not even decimal; none part of them is a character. :-)

And your question is probably very good; I just don't have enough qualification to evaluate it. :-)

—SA

Christian Graus 24-Dec-13 2:56am

Well, you're being obtuse. Digits have no . in them. The number 3.5 clearly does have one.

Sergey Alexandrovich Kryukov 24-Dec-13 2:20am

Very good, thank you.
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2013-12-23T19:31:00

Solution 2

SA is right, numbers do not have more than one decimal. RK is right, for clarity, the answer he's referring to, is the one that casts your values to the heirarchyid[^] type.

SQL

select * from yourtable ORDER BY CAST('/' + yourVersionNumber + '/' AS hierarchyid)

Posted 23-Dec-13 19:31pm

Christian Graus

Comments

Maciej Los 24-Dec-13 2:01am

+5

Rakesh Meel · Accepted Answer · 2013-12-23T19:37:00

Solution 3

visit here........

http://stackoverflow.com/questions/17324903/sort-order-on-column-with-multiple-decimal-places[^]

Posted 23-Dec-13 19:37pm

Rakesh Meel

Comments

Christian Graus 24-Dec-13 1:57am

I upvoted this because it was correct, I did not know the answer, I just copied it for clarity and so the answer is here, even if it disappears there. I do suggest you both explain your answer, and provide your link, if you have one.

[no name] 24-Dec-13 3:16am

Thanks .....Sir..

Maciej Los 24-Dec-13 2:01am

+5

[no name] 24-Dec-13 3:16am

Thanks .....Sir