Extracting a String Within Delimeters - Part 2

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colu

  • There is a typo in the SELECT code, needs to be


    SELECT Particulars,


           /* <text>> */ Particulars,

           /* <start > */   CHARINDEX(';',Particulars,1) + 1,

           /* <length> */   CHARINDEX(';',

              SUBSTRING(Particulars, CHARINDEX(';', Particulars, 1) + 1, 99) + ';') - 1

          ) ) ) AS Result_string

    FROM Sample_table

  • The innermost SUBSTRING will yield this:

     LUC20031026901 ; Vehicle


     SUB35030172701 ; Building

     LIQ200310121 ; Liquor

    and when the semicolon is added, it will look like this:

     LUC20031026901 ; Vehicle                                                                          ;

     Consolidated                                                                                      ;

     SUB35030172701 ; Building                                                                         ;

     LIQ200310121 ; Liquor                                                                             ;


    Best Regards,

    Code Monkey

  • Wouldn't it be easier to just use:

    SELECT RIGHT(RTRIM(Particulars), LEN(RTRIM(Particulars)) - CHARINDEX(';',Particulars) - 1)

    FROM Sample_table

    Also, you could remove the RTRIMs if you used VARCHAR instead of CHAR...

    Adam Machanic

  • drop table Sample_table

    CREATE TABLE #Sample_table (Particulars varchar(120))


    INSERT INTO #Sample_table VALUES('LDR ; LUC20031026901 ; Vehicle')

    INSERT INTO #Sample_table VALUES('LDR ; Consolidated                                                     ')

    INSERT INTO #Sample_table VALUES('LDR ; SUB35030172701 ; Building')

    INSERT INTO #Sample_table VALUES('LDRR ; LIQ200310121 ; Liquor')

    select * from #Sample_table

    SELECT Particulars,

    --    quotename( -- enable this to see, that's just the string without spaces around



       /*from*/CHARINDEX(';',Particulars,1)+1,   -- search first delimiter and then offset 1 from first deliminter

       /* to */CHARINDEX(';',                    -- search for the next delimter as the end-of-string

         SUBSTRING (Particulars,

             /*from*/    (CHARINDEX (';', Particulars,1)+1) -- the first character after first delimiter

             /* to */   , datalength(Particulars)     -- use datalength for not beeing bound to specific length

             ) + ';'  -- for any case add a delimiter (this was a good idea from Denis Oliynik !!), if it's an additonal, it's just lost

          ) - 1  -- delimiter Position minus one    


         ) -- rigth parenthesis for lrim

           ) -- rigth parenthesis for rtrim

    --&nbsp rigth parenthesis for quotename

    FROM #Sample_table

    /* commet out ltrim/rtrim to extract the whole string with spaces

    This is a very general select not delimited to programmed stringlenthes or varchar's etc. It just extracts the first delimited string within another string, not more or less.


    Norbert L.Muth, M.A.

    DBA of KNO-VA.de



  • Thanks for the feedback. The typo occurred in translation from the submitted MS/Word document to the HTML page and was an accident by the publisher that I did not pick up on. Please accept my apologies for this.

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

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