IP addresses with port numbers, need to extract class c addresses

[txwylde]

I have a huge list of 8500 ip addresses. In some instances, they have port numbers. I have tried importing the list in a temp table and then doing a substring but it still gives me some lines with extra spaces. For example, I did a
select substring(ip, 1, 11) from test

being the class c address is XXX.XXX.XXX which is 11 characters. There are some IPs which are like 64.2.7 which is 7 characters.

Is there a way I can get just the class c without having to manually go through to delete the extra characters?
Thanks!
Bill

 

[hoinz]

In case of 64.2.7 (6 characters, imho): What is in position 7?
Let's assume there is always a blank behind the IP address. Then one approach could be:

Use instr() to find the position of the first blank.
Than use substr() to choose only the bytes left of this postion.

hope this helps

 

[txwylde]

Apparently there are no blanks in the numbers since I cut and pasted the IPs and put these IPs into TextEdit. I have used the substring to try remove the extra IP addresses.

 

[ingresman]

You might be able to do something in mysql but you need to detect the position of 2 moving "dots". If you had only one dot it would be fairly easy, if a bit tedious.
A little known thing in SQL (and mysql supports it) is selecting the same column more than one in a query so you could issue a select ipaddress as ip, ipaddress bb from .... and then work on each one locating the first dot and then guessing where the second might be in the seconmd column but that will be fraught with problems e.g. if you had ipaddress like;
1.1.1.1 and
345.56.1.7
You could not predict where the second dot might start unless becase of the way your IPaddress are stored you might get away with it.
Assuming you need to do this one for a bit of housekeeping or its for your own use you could knock out a php script to select from the database then use explode() on each IPaddress delimited by "." and you should be flying then.
If your not a PHP guy have a look on the PHP documentation for the mysql functions and the user contributed contributed notesd have loads of examples so I'm sure you could pull something together. If you get to the stage where your stuck post something in the PHP forum and someone will help out.
finally you might be able to code a stored proc to do the work, but I've not written a mysql stored proc yet
Good luck !