Post reply

Parsing a character string

  • December 21, 2005 at 9:31 am

    #90183

    Hi,

    Can someone help me with some coding?  I need to parse through a character like the following:

    EXISTING SERVICE-DESKTOP HARDWARE-PC

    I need to pull out the last section of that code (the "PC" part) into it's own field into anothe table.  I found where I can parse the above using

    substring(Type,

                           charindex('-',Type + '-')+1 ,

                           len(Type)) as [Type2]

    but that gives me:   DESKTOP HARDWARE-PC

     

    Any help would be greatly appreciated.  Thanks Terry

  • December 21, 2005 at 9:40 am

    #610898

    Try

    SELECT RIGHT(Type,CHARINDEX('-',REVERSE(Type))-1)

    Mark

  • December 21, 2005 at 11:06 am

    #610915

    Thanks Mark,

    I get the following error when running the above statement:

     

    Server: Msg 536, Level 16, State 1, Line 1

    Invalid length parameter passed to the substring function.

     

    Doesn't like the -1 in the charindex.  I tried 0 but it includes the "-" and +1 puts me on the left side of the dash????

  • December 21, 2005 at 3:32 pm

    #610980

    You will get that error if there is no '-' in TYPE.  You can get around that by doing a check first.

    SELECT

      CASE CHARINDEX('-',type)

        WHEN 0 THEN ''

        ELSE RIGHT(Type,CHARINDEX('-',REVERSE(Type))-1)

      END

     

     

  • December 22, 2005 at 7:49 am

    #611129

    Thanks so much Mark.  I ended up using a vairation.  Thanks for pointing me in the "right" direction.

    substring(right(i.Type,CHARINDEX('-', reverse(i.Type))), 2, 20) as Category

    Terry  

  • December 22, 2005 at 8:21 am

    #611151

    Just for fun, here's another variation on that topic

    SELECT

     PARSENAME(REPLACE('EXISTING SERVICE-DESKTOP HARDWARE-PC', '-','.'),1)

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 22, 2005 at 9:13 am

    #611163

    Here is another interesting way:

    select Type2=substring(Type,charindex('-',Type,charindex('-',Type)+1)+1,

      len(Type) - charindex('-',Type,charindex('-',Type)+1))

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

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