String Function Help

  • Hello,

    Total Newbie to SQL Server, so pardon any non synchronous sql lingo.

    In a table, i have column "IP Address"

    which has values of ip address such as

    102.89.93.101

    100.100.10.1

    123.94.9.121

    etc

    what I want SQL Server to do is return the first 3 values after the second dot.

    So it should return

    102.89.93

    100.100.10

    123.94.9

    How to make possible?

    Thanks in advance for any help.

  • Many ways. Choose your poison:

    DECLARE @T TABLE (IP VARCHAR(30))

    INSERT INTO @T

    SELECT '102.89.93.101'

    UNION ALL SELECT '100.100.10.1'

    UNION ALL SELECT '123.94.9.121'

    SELECT PARSENAME(IP, 4) + '.' +

    PARSENAME(IP, 3) + '.' +

    PARSENAME(IP, 2)

    FROM @T

    SELECT LEFT(IP, LEN(IP)-CHARINDEX('.', REVERSE(IP)))

    FROM @T


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thanks Dwain.

    I tried

    select LEFT(127.0.0.1,len(127.0.0.1-charindex('.',reverse(([Ip]))

    from [dbo].[TestTable]

    but am getting

    Incorrect syntax near the keyword 'from'.

    127.0.0.1 is column name.

    Since I'll probably have a list of over 100k I don't think the other options would be practical.

  • VegasL (11/18/2012)


    Thanks Dwain.

    I tried

    select LEFT(127.0.0.1,len(127.0.0.1-charindex('.',reverse(([Ip]))

    from [dbo].[TestTable]

    but am getting

    Incorrect syntax near the keyword 'from'.

    127.0.0.1 is column name.

    Since I'll probably have a list of over 100k I don't think the other options would be practical.

    You need to make sure your parentheses are properly matched:

    SELECT LEFT(127.0.0.1, LEN(127.0.0.1)-CHARINDEX('.', REVERSE(127.0.0.1)))

    FROM [dbo].[TestTable]


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • ( problem in your query

    select

    LEFT(127.0.0.1,len(127.0.0.1)-charindex('.',reverse(([Ip]))))

    from tablename

  • Thanks for you're help Brian. That worked. I am now trying to show which IP's are used more than once, so I tried

    select LEFT(127.0.0.1,len(127.0.0.1)-charindex('.',reverse(([Ip]))))

    from tablename

    group by 127.0.0.1

    having count (127.0.0.1)>1

    order by (127.0.0.1) desc

    It does display the 127.0.0.1 's used more than once but it is also displaying some that are only used once. Any thoughts?

  • your query seems ok and working fine

    may be you are selecting LEFT(127.0.0.1,len(127.0.0.1)-charindex('.',reverse(([Ip])))) which may leads you to confusion

    you can try putting ip in select and find where's the problem

    select LEFT(127.0.0.1,len(127.0.0.1)-charindex('.',reverse(([Ip])))),ip

    from @t

    group by 127.0.0.1

    having count (127.0.0.1)>1

    order by (127.0.0.1) desc

    or else post some sample data in which you are facing problem.

Viewing 7 posts - 1 through 6 (of 6 total)

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