1. Registered User
Join Date
May 2002
Location
USA
Posts
9

hi all

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.

Krish+
v

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Are they always 4 position version numbers?

3. Registered User
Join Date
May 2002
Location
USA
Posts
9
Yes there r always 4 position numbers

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I liked my answer there, athough I can take a stab at answering it here too.

The problem lies in the fact that version numbes are typically strings of pairs of numbers, each one of which has to be interpreted as an integer. Each pair is atomic, it can't be split. Each pair also depends on the preceeding (further left) pairs.

The problem has been debated for ages in the Unix community. The numbering scheme is easy for humans to use, but a challenge for machines to manipulte without specific routines that understand the notation.

-PatP

5. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Well if they are

Code:
```USE Northwind

CREATE TABLE myTable99(Col1 varchar(8000))
GO
INSERT INTO myTable99(Col1)
SELECT '1.5.5.19'  UNION ALL
SELECT '1.5.5.9'   UNION ALL
SELECT '1.5.15.19' UNION ALL
SELECT '1.25.5.9'
GO

SELECT *
FROM myTable99
ORDER BY
CONVERT(int,PARSENAME(Col1,4))
,CONVERT(int,PARSENAME(Col1,3))
,CONVERT(int,PARSENAME(Col1,2))
,CONVERT(int,PARSENAME(Col1,1))
GO

DROP TABLE myTable99
GO```
As stolen from:

Damain's Article

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
That is deviant! I LIKE it!

-PatP

7. Registered User
Join Date
May 2002
Location
USA
Posts
9
Does it work for MySQL too or is there any PARSENAME equivalent there?

Kris

8. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Well I would try:

http://dev.mysql.com/doc/

Or

http://www.dbforums.com/f5

Did you expect to find a big mac at a 4 star restaurant?

If they don't have it's gonna be a matter of parsing and build functions...do they have functions yet?

All kidding aside...good luck....

9. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
OK,

Look at

http://dev.mysql.com/doc/mysql/en/String_functions.html

And The MAKE_SET function...looks close...

But you should keep that online manual open all the time

10. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
This just in...mySQL supports UNION in latest release!

http://dev.mysql.com/doc/mysql/en/UNION.html

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•