Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2003
    Posts
    29

    Unanswered: Drop the end of an ip address

    does anyone know how to drop the last part of an ip address? I've been searching various sites for string functions and havn't come up with anything that works. What I want is something that will "find the third occurance of a "." and then return everything to the left of it"

    I've tried various types of InStr and even found a SUBSTRING_INDEX function, but apparently it's only for mysql.

    i'm using sql server and linking tables thru access

    fisk

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you can do reverse and then a right with a nested charindex. with another reverse at the end.

    or you can stuff with a nested charindex.
    Last edited by Thrasymachus; 03-07-06 at 12:13.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2003
    Posts
    29
    i'm getting "undefined function" error.

    SELECT dbo_AeXInv_AeX_AC_TCPIP.WrkstaId, REVERSE(dbo_AeXInv_AeX_AC_TCPIP.[IP Address]), dbo_AeXInv_AeX_AC_TCPIP.[Host Name]
    FROM dbo_AeXInv_AeX_AC_TCPIP;


    the reverse string function seems pretty simple, but I must be missing something.

    fisk

  4. #4
    Join Date
    Apr 2003
    Posts
    29
    Same error message for charindex

    SELECT dbo_AeXInv_AeX_AC_TCPIP.WrkstaId, charindex('.', dbo_AeXInv_AeX_AC_TCPIP.[IP Address]), dbo_AeXInv_AeX_AC_TCPIP.[Host Name]
    FROM dbo_AeXInv_AeX_AC_TCPIP;


    I just ran this in sql analyzer and it worked fine. Any idea why i'm getting this error in access?

    fisk

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    microsoft access does not use the same functions as microsoft sql server

    i mean, you would think it would, what with sql standards and all, but you gots to remember, microsoft bought access from a different company than it bought sql server from



    as long as we're playing loosey-goosey with database platforms, why don't you run it on mysql? it has a wonderful function called SUBSTRING_INDEX

    select substring_index(`IP Address`, '.', 3) as stuff_before_the_third_period
    from dbo_AeXInv_AeX_AC_TCPIP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2003
    Posts
    29
    I would have thought that access would just pass the sql statement to sql server and let it figure it out. I guess not.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it will only do that if you have declared it as a pass-through query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not quiet. For linked tables, Access will attempt to format the statement as a pass-through query on its own, but if it cannot (and it cannot handle anything more than moderately complex) then it will execute it locally (and ineffeciently).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2003
    Posts
    29
    Yep, that's pretty much what's happening.

    Oh well, thanks anyway guys.

    fisk

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    For linked tables, Access will attempt to format the statement as a pass-through query on its own
    holey moley

    i've been using access with linked tables for about 10 years, and i didn't know that

    so, what does it do when the sql contains an access function like InStr()?

    it barfs, right?

    so when you say "moderately complex" you really mean "anything even slightly more complex than SELECT foo FROM bar WHERE qux=937", eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's somewhere in the documentation. I don't know how complex it can get before it has to run it locally. It may depend upon whether it has embedded functions, or it may depend upon whether it is able to succesfully translate MS Access SQL to TSQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should try some simple queries and then monitor server activity to see what gets sent through.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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