Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Lightbulb Unanswered: Urgent help with SQL query!

    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

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Visual Basic 6,

    Dim str As String
    str = "192.168.0.255"
    str = Left(str, InStrRev(str, ".")) + "1"
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •