Need help with Invalid length parameter passed to the substring function

Posted on 2006-03-26
Last Modified: 2008-02-01
I need to parse out several lines of email headers contained in a text type column

Here's an example

Date: Thu, 27 Apr 2000 09:05:22 -0400
From: John Doe <>
Subject: test
To: <>
Mime-Version: 1.0
Organization: Company, Inc.

I need to pull the Date:, From: Subject: and To: lines to their own columns. I know how to use SUBSTRING(EmailText,PATINDEX('%From:%',EmailText),40) to get 40 chars of the From: line but would like to pull everything up to the linefeed for each of the above.

I accepted the following as a solution previously but discovered it includes the trailing char(10) which shows up in the column as a trailing pair of boxes when I SELECT INTO a new table:

SELECt SUBSTRING(EmailText,PATINDEX('%From:%',EmailText),PATINDEX('%'+CHAR(10)+'%',SUBSTRING(EmailText,PATINDEX('%From:%',EmailText),Len(EmailText))))
I tried the following but I get "Invalid length parameter passed to the substring function" due to missing data in certain rows:

SELECt SUBSTRING(EmailText,PATINDEX('%From:%',EmailText),PATINDEX('%'+CHAR(10)+'%',SUBSTRING(EmailText,PATINDEX('%From:%',EmailText),Len(EmailText)))-2)

I tried eliminating incomplete headers through the WHERE clause by making sure that the strings 'From:','To:','Subject:' and 'Date:' are in the header but sometimes they appear with no following text which results in the invalid length parameter passed error.
Question by:dsteinschneider
    LVL 75

    Accepted Solution

    You may want to resort to using a function like this:

    CREATE Function dbo.udf_GetEmailText(
                @EmailText text,
                @Field varchar(20))

    Returns varchar(1000)

    Declare @FromPos integer,
          @ToPos integer,
          @LF char(1),
          @Value varchar(1000)

    Set @LF = CHAR(10)

    Set @FromPos = CHARINDEX(@Field, @EmailText)
    If @FromPos > 0
          Set @FromPos = @FromPos + LEN(@Field)
          Set @ToPos = CHARINDEX(@LF, @EmailText, @FromPos)
          If @ToPos = 0
                Set @ToPos = DATALENGTH(@EmailText)
          Set @Value = SUBSTRING(@EmailText, @FromPos, @ToPos - @FromPos + 1)
          Set @Value = Null       -- Or '' whichever you prefer

    Return @Value

    You can then call it as follows:

    Select      ID,
          dbo.udf_GetEmailText(EmailText, 'Date:'),
          dbo.udf_GetEmailText(EmailText, 'From:'),
          dbo.udf_GetEmailText(EmailText, 'Subject:'),
          dbo.udf_GetEmailText(EmailText, 'To:'),
          dbo.udf_GetEmailText(EmailText, 'Mime-Version:'),
          dbo.udf_GetEmailText(EmailText, 'Organization:')
    From      YourTableName
    LVL 75

    Expert Comment

    by:Anthony Perkins
    The reason I suggest you do it this way, is that it will give you greater flexibility in modifying it.  For example, you may find you are getting false positives:  The "From:" may be embedded in the text somewhere.  What you would have to do then is search for CHAR(10) + 'From:'

    Nome other notes:
    1.  Since the column is a Text datatype use DATALENGTH instead of LEN
    2.  I am making the assumption all the "lines" end in CHAR(10), except for optionally the last.
    3.  If you return Null instead of '' it will alert you to the fact that the "field" did not exist as opposed to being really empty.
    LVL 1

    Author Comment

    I agree with you three notes. The way you wrote the function makes it very clear. It works well except that each column has a leading space. I tried changing this:

    Set @Value = SUBSTRING(@EmailText, @FromPos+1, @ToPos - @FromPos-2)

    Looked good for the top 2000 rows but somewhere beyond that it resulted in the invalid parameter for substring error.

    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>It works well except that each column has a leading space. <<
    Right that is because there is a space after every "Field".  If you do not need the space, just change the query to (assuming that all "Fields" have a space at the end):
    Select     ID,
         dbo.udf_GetEmailText(EmailText, 'Date: '),
         dbo.udf_GetEmailText(EmailText, 'From: '),
         dbo.udf_GetEmailText(EmailText, 'Subject: '),
         dbo.udf_GetEmailText(EmailText, 'To: '),
         dbo.udf_GetEmailText(EmailText, 'Mime-Version: '),
         dbo.udf_GetEmailText(EmailText, 'Organization: ')
    From     YourTableName
    LVL 1

    Author Comment

    I had tried already to modify the query by altering the second parameter but I still got the leading space.

    I realized my last attempt to shift the @FromPos and @ToPos was faulty - here's the change that works:

    Set @Value = SUBSTRING(@EmailText, @FromPos+1, @ToPos - (@FromPos+1))

    Thanks again for your help - this is for a compliance report.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now