Post reply

Substring Error

  • April 13, 2010 at 3:51 am

    #140372

    I am currently trying to format some log data inputted into a staging table. I effectively read each line of the log text file into a table column and then try and split the string. Below is an example of the text in the column which is called LineRead.

    [Mon Mar 02 08:54:02 2009]Local/GMR09///Info(1013205)

    [Mon Mar 02 08:54:49 2009]Local/GMR09/OTLCHK/admin/Info(1021006)

    [Mon Mar 02 09:32:32 2009]Local/GMR09///Info(1013210)

    For the 2nd row shown above I need to split the string into separate fields containing

    Mon Mar 02 08:54:49 2009,Local,GMRO9,OLTCHK,admin. I ignore rows which contain '///'

    The SQL I am using to do this is shown below

    SELECT

    SUBSTRING(LineRead,2,24)

    ,SUBSTRING(

    LineRead,

    CHARINDEX('/',LineRead)+1,

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)-1 - CHARINDEX('/',LineRead))

    ,SUBSTRING(

    LineRead,

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1,

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)-1

    - CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1))

    ,SUBSTRING(

    LineRead,

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1) +1,

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)+1)- 1 -

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1) +1))

    FROM dbo.stg_LogFiles

    WHERE CHARINDEX('[',LineRead) = 1

    AND NOT SUBSTRING(LineRead,

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)

    ,2) = '//'

    AND SUBSTRING(LineRead,2,3) IN ('mon','tue','wed','thu','fri','sat','sun')

    When compiling I keep getting this error:

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

    Invalid length parameter passed to the substring function.

    I am not sure how to rectify this.

    Any help would be much appreciated.

  • April 13, 2010 at 4:05 am

    #1150579

    why don't you use replace function?

    Try this one...

    Declare @STR varchar(255)

    select @STR = '[Mon Mar 02 08:54:49 2009]Local/GMR09/OTLCHK/admin/Info(1021006)'

    select replace(@str,'/',',')

    karthik

  • April 13, 2010 at 4:06 am

    #1150580

    See if this query returns any negative values

    SELECT

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)-1 - CHARINDEX('/',LineRead)

    ,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)-1

    - CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)

    ,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)+1)- 1 -

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1) +1)

    FROM dbo.stg_LogFiles

    WHERE CHARINDEX('[',LineRead) = 1

    AND NOT SUBSTRING(LineRead,

    CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)

    ,2) = '//'

    AND SUBSTRING(LineRead,2,3) IN ('mon','tue','wed','thu','fri','sat','sun')

    This is actually the 3rd parameter you have passed to the 3 substring functions. These should never be negative..

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • April 13, 2010 at 4:30 am

    #1150592

    I have found some negatives and have amended the query to check this. Presumabaly I need to do this for all the parameters passed to the substring?

  • April 13, 2010 at 4:48 am

    #1150601

    You will have to do this for the 2nd and 3rd parameter. But in your case the 2nd parameter cannot be negative. But you can still check to be sure..

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • April 13, 2010 at 4:52 am

    #1150602

    Many thanks. Much appreciated.

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

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