SQL help needed!!!

  • Thread starter Thread starter Serge
  • Start date Start date
S

Serge

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
 
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!
Click to expand...

If you have A2000 or later, there's an InstrRev (in-string reverse)
function which starts at the *right* of a string instead of the left.
Try updating Gateway to

Left([IP Address], InStrRev([IP Address], ".") & "1"
 
Hi John!
thank you for the promt reply. I think the closing
bracket is missinig, after "1", correct? It should be:
Left([IP Address], InStrRev([IP Address], ".") & "1")

I tested it, and it simply copied the ip address
to "gateway" column. Have you tested it, is it succeful?
Please help!!!
-----Original Message-----
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!
Click to expand...

If you have A2000 or later, there's an InstrRev (in- string reverse)
function which starts at the *right* of a string instead of the left.
Try updating Gateway to

Left([IP Address], InStrRev([IP Address], ".") & "1"



.
Click to expand...
 
Hi John!
thank you for the promt reply. I think the closing
bracket is missinig, after "1", correct? It should be:
Left([IP Address], InStrRev([IP Address], ".") & "1")
Click to expand...

No. I did leave out a close paren (sorry!) but it should be to the
LEFT of the ampersand.
I tested it, and it simply copied the ip address
to "gateway" column. Have you tested it, is it succeful?
Please help!!!
Click to expand...

Well, you got me worried so I did try:

?instrrev("333.444.55.6",".")
11
?Left("333.444.55.6",instrrev("333.444.55.6","."))
333.444.55.
?Left("333.444.55.6",instrrev("333.444.55.6",".")) & "1"
333.444.55.1


What is the actual value in the IP address field?
 
Thank you John!!!!! It works!
-----Original Message-----
Hi John!
thank you for the promt reply. I think the closing
bracket is missinig, after "1", correct? It should be:
Left([IP Address], InStrRev([IP Address], ".") & "1")
Click to expand...

No. I did leave out a close paren (sorry!) but it should be to the
LEFT of the ampersand.
I tested it, and it simply copied the ip address
to "gateway" column. Have you tested it, is it succeful?
Please help!!!
Click to expand...

Well, you got me worried so I did try:

?instrrev("333.444.55.6",".")
11
?Left("333.444.55.6",instrrev("333.444.55.6","."))
333.444.55.
?Left("333.444.55.6",instrrev("333.444.55.6",".")) & "1"
333.444.55.1


What is the actual value in the IP address field?



.
Click to expand...
 
Back
Top