Post reply

select case statement

  • May 10, 2011 at 4:19 pm

    #237264

    Have a table1 with following records

    Server name

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

    w112 n1

    w110 n2

    radon1 n3

    radon1b.abc.com n4

    radon1c.abc.com n5

    radon1d.abc.com n6

    select name,

    case when len(server)>14

    then substring(server,1, charindex('.', server)-1)

    else server

    end

    from table1

    My goal is to retreive the records in the following format

    w112

    w110

    radon1

    radon1b

    radon1c

    radon1d

    I am getting an error message

    Invalid length parameter passed to the LEFT or SUBSRING function.

    Can you please help identifying what I am doing wrong.

    Thanks in advance

    Jay

  • May 10, 2011 at 4:49 pm

    #1322681

    The issue is that you most probably have a string with a lenght >14 but without a '.' in between. This will lead to substring(server,1, -1) leading to the error you see.

    One option is to use a CASE statement to cover this scenario:

    SELECT SUBSTRING(server,1, CASE WHEN CHARINDEX('.', server)>0 THEN CHARINDEX('.', server) -1 ELSE 0 END)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 10, 2011 at 4:56 pm

    #1322683

    Something like this?

    DECLARE @Table TABLE

    ( Server VARCHAR(250), name VARCHAR(20) )

    INSERT INTO @Table

    VALUES ('w112','n1')

    , ('w110','n2')

    , ('radon1', 'n3')

    , ('radon1b.abc.com', 'n4')

    , ('radon1c.abc.com','n5')

    , ('radon1d.abc.com','n6')

    SELECT REVERSE( PARSENAME ( REVERSE( Server + '.') , 1)) FirstPartOfTheString

    FROM @Table

  • May 10, 2011 at 5:05 pm

    #1322687

    As Lutz pointed it out, the problem is with the full stop not being present for some rows 🙂

    {Edit: Sorry for the typo, it was Lutz not Lowell :-P}

  • May 10, 2011 at 5:08 pm

    #1322689

    Thank you all for the reply & suggestions

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

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