Post reply

String Functions

  • November 21, 2007 at 10:37 am

    #180679

    Hello All

    I am working with some strings that are stored in the database. Not my most favorite thing. But here is what I have and what I am looking for. I have a table that is storing IP Addresses. I would like to be able to select the second and third octet from the IP Address.

    Here is an example: 10.3.5.15, or 10.25.215.10

    So I cannot fully use the substring function for this. Since every IP address has a different length. I would like to select the 3, or the 5, or the 25, or the 215 Each IP address does have a period in the string separating the octets.

    Thanks in advance

    Andrew

  • November 21, 2007 at 10:53 am

    #753179

    declare @ip varchar(15)

    select @ip='1.10.20.30'

    select substring(@ip,(charindex('.',@ip)+1),(len(@ip)-(CHARINDEX ('.',@ip) + CHARINDEX ('.',REVERSE(@ip)))))


    Nuke the site from orbit, its the only way to be sure... :w00t:

  • November 21, 2007 at 11:17 am

    #753191

    That is very close. But what I needed was each octet of the IP address in a separate select.

    IP Address = 10.5.215.12

    like this:

    select the second octet AS secondOctet, third octet AS ThirdOctet, fourth octet AS FourthOctet

    SecondOctet ThirdOctet FourthOctet

    5 215 12

    Thank You for what you gave me, I will work with that and see if I can get the query to return the string in this fashion. Sorry if my first post was not clear about that.

    Thanks again

    Andrew

  • November 21, 2007 at 11:25 am

    #753194

    How about ...

    DECLARE @ipList TABLE (ipAddress VARCHAR(15))

    INSERT @ipList

    SELECT '68.251.41.3' UNION SELECT '10.0.5.112' UNION SELECT '192.168.7.1' UNION SELECT '127.0.0.1'

    SELECT

    PARSENAME(ipAddress,4) AS firstOctet,

    PARSENAME(ipAddress,3) AS secondOctet,

    PARSENAME(ipAddress,2) AS thirdOctet,

    PARSENAME(ipAddress,1) AS fourthOctet

    FROM

    @ipList

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 21, 2007 at 11:34 am

    #753196

    Jason... that is brilliant! You have no idea how long I spent trying to substring, charindex... what a mess.

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • November 21, 2007 at 11:40 am

    #753197

    I learned it from watching these forums.

    It's actually the keyword to parse out object names, and just happens to work with IP addresses.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 21, 2007 at 12:46 pm

    #753202

    I completely agree. For example i now know to use parsename instead of CHARINDEX AND SUBSTRING to sort out the octets. 🙂


    Nuke the site from orbit, its the only way to be sure... :w00t:

  • November 21, 2007 at 2:46 pm

    #753222

    Thanks Jason

    That did perfectly, just what I needed.

    Have a good one

    Andrew

  • November 21, 2007 at 4:50 pm

    #753244

    How about ...

    Perfect...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply