Parsing Periods in the column

[akrishmohan]

Hi an Interesting question

I have a column which stores a versin number in this format

1.5.5.19
1.5.5.9
...
...

I want to be able to sort this text column in an ascending order. Unfortunately it gives me 1.5.5.19 followed by 1.5.5.9 which is not the case.

Please let me know ASAP

Krish+

 

[duncdude]

have I gone nuts?

[/b].9 is greater than .19[/b]

... do you mean 'descending' - or am I having a bad day???


Kind Regards
Duncan

 

[TonyGroves]

Try the following:
[tt]
SELECT ip
FROM tbl
ORDER BY
CONCAT(
LPAD(SUBSTRING_INDEX(ip,'.',1),3,0),
LPAD(
SUBSTRING_INDEX(
SUBSTRING_INDEX(ip,'.',2),'.',-1
),
3,0
),
LPAD(
SUBSTRING_INDEX(
SUBSTRING_INDEX(ip,'.',3),'.',-1
),
3,0
),
LPAD(SUBSTRING_INDEX(ip,'.',-1),3,0)
)
[/tt]
This might seem a bit overkill, but it will allow for up to three digits in each of the four sections of the string, and sort accordingly. It also works for IP addresses.


-----
ALTER world DROP injustice, ADD peace;