Urgent help with SQL query!

SERGE_2004 · #1 (**permalink**) 02-02-04, 17:27

Hi everyone!

I have the table which has IP address column
(192.xxx.xxx.xxx) And I have a new blank column in the
same table titled "Gateway". I will need help to create
the query which will take IP address and replace the last
octet of IP address to .1 , so the results look like :
IP address Gateway
192.134.45.67 192.134.45.1
192.56.25.204 192.56.25.1
And copy that data later into corresponding row
of "gateway" column.
The problem is that last octet could have one, two or
three characters and you can't use here RIGHT, REPLACE is
also very tricky.
Does anyone have any ideas how to do it?
Any help will be greatly appreciated!
Million thanks!

Serge

r123456 · #2 (**permalink**) 02-02-04, 20:50

Visual Basic 6,

Dim str As String
str = "192.168.0.255"
str = Left(str, InStrRev(str, ".")) + "1"

Littlefoot · #3 (**permalink**) 02-04-04, 03:13

Oracle's SQL:

Code:

UPDATE ip_table SET
  gateway = SUBSTR(ip, 1, INSTR(ip, '.', 1, 3)) || '1';

The INSTR(char1, char2 [,n[,m]]) function searches "char" beginning with its "n"th character for the "m"th occurrence of "char2" and returns the position of the character in "char" that is the first character of this occurrence.

"||" represents concatenation operator.

For example, this query would do something like that:

Code:

IP                    GATEWAY
------------------ ---------------
192.134.45.67   192.134.45.1

Internet Services

Web Development

Digital Marketing

Consumer Tech