Post reply

Query Help

  • July 2, 2012 at 3:01 pm

    #258742

    how to get the desired output from the following table

    TableA

    Ip

    -------------------------------

    5.46.200.1.462222.2.1.2.3.4.5.1

    5.46.200.1.462222.2.1.2.3.4.5

    Expected output:

    Ip

    -------------------------------

    5.46.200.1.462222.2.1.2.3.4.5

    5.46.200.1.462222.2.1.2.3.4

    Thanks for you help in advance !

  • July 2, 2012 at 3:10 pm

    #1507941

    Something like this should work. Remove the CTE porition and replace the CTE name (TestData) with your table name.

    with TestData as (

    select lp from

    (values

    ('5.46.200.1.462222.2.1.2.3.4.5.1'),

    ('5.46.200.1.462222.2.1.2.3.4.5')

    ) sampledata(lp)

    )

    select

    reverse(substring(reverse(lp), charindex('.',reverse(lp)) + 1, datalength(lp))) as lp

    from

    TestData;

  • July 2, 2012 at 3:14 pm

    #1507944

    here's my version, but i was assuming you wanted to cop off the last period and whatever was to the right of it:

    I left a lot of intermediate calculations to help you visualize it:

    WITh MyCTE (Ip )

    AS

    (

    SELECT '5.46.200.1.462222.2.1.2.3.4.5.1' UNION ALL

    SELECT '5.46.200.1.462222.2.1.2.3.4.5'

    )

    --chop off the last substring to the right of the period?

    SELECT

    * ,

    REVERSE(IP) as reversed,

    SUBSTRING(REVERSE(IP),1,CHARINDEX('.',REVERSE(IP))) as TheFirstPart,

    SUBSTRING(REVERSE(IP),CHARINDEX('.',REVERSE(IP)),50) TheLastPartAlmost,

    SUBSTRING(REVERSE(IP),CHARINDEX('.',REVERSE(IP)) + 1,50) as TheLastpart,

    REVERSE(SUBSTRING(REVERSE(IP),CHARINDEX('.',REVERSE(IP)) + 1,50)) TheLastPartFixedandReversed

    from MyCTE

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 2, 2012 at 3:28 pm

    #1507947

    Thanks a lot it works !!

Viewing 4 posts - 1 through 3 (of 3 total)

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