How to Trim Data (IP Address)? 1

[kudithipudi]

Hello Guys,
Have a question about trimming data. I have a column in a table with IP addresses as such

IP_Address
192.168.2.51
192.168.4.53
192.168.2.50
192.168.2.49

I want to take this data, remove the fourth octect and inset the new string into another table. Is there a way to do this in SQL? I know the part of inserting the data into another table. But am not sure how to trim the data. Any help is appreciated. Thanks much.

- V.

 

[carp]

How about
INSERT INTO my_other_table
SELECT SUBSTR(ip_address,1,INSTR(ip_address,'.',3)-1)
FROM my_table;

This would work in Oracle, but I'm not sure about other RDBMSs.

 

[carp]

Well, not quite!

Try this instead:

INSERT INTO my_other_table
SELECT SUBSTR(ip_address,1,INSTR(ip_address,'.',1,3)-1)
FROM my_table;

 

[kudithipudi]

Carp - Thanks for the answert. But I am using SQL 2000 , and the query you have provided does not seem to run in it.

- V.

 

[carp]

OK - sorry to hear that. But I have to believe that SQL2000 has a substring and instring function, so you should be able to find an equivilent statement without too much trouble.

 

[tlbroadbent]

I recommend forum183 for SQL Server questions.

The following T-SQL code will remove the 4th octet from the ip_address.

Select Left(ip_address,len(ip_address)-charindex('.',reverse(ip_address))) If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[kudithipudi]

tlbroadbent - Thanks for help. Exactly what I was looking for.

- V.