String Ordering

[mrJazz]

Hi,

Just wondering what the best way of doing the following is...

I have a table with IP addresses in, stored as VARCHAR. I would like to order these address correctly..

Is there a query function that will split on the dot then order the strings as if they were INT's?

Thanks

[Bunkermaster]

alpha sorting will handle this perfectly no need to explode your data...

[EDIT]damn typos[/EDIT]

[[deleted]]

Try something like this:

SELECT ip,
substring_index(ip,'.',1) + 0 as part1,
substring_index(substring_index(ip,'.',2),'.',-1) + 0 as part2,
substring_index(substring_index(ip,'.',3),'.',-1) + 0 as part3,
substring_index(ip,'.',-1) + 0 as part4
FROM tablename
ORDER BY
part1,
part2,
part3,
part4

[tomhath]

Should be able to do it with nested SUBSTRING_INDEX() and CAST, CONVERT, or LPAD functions:

CAST(SUBSTRING_INDEX('1.2.3.4', '.', 1) as signed)
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX('1.2.3.4', '.', 2),'.',-1) as signed)

http://www.mysql.com/doc/en/String_functions.html#IDX1180

[tomhath]

:mad: Matt beat me to it...

[mrJazz]

Yep... works like a charm... thanks

ed