none
Error with substring seperating data by char(10)+char(13) line breaks RRS feed

  • Question

  • What I am attempting to do is separate my data by the line breaks into separate fields: Attn, Addr1Field, Addr2Field. I have found the location of both of the line breaks but the difference isn't the same for every row of data so I'm using the expression as the third option in my Substring() function. I'm getting the error Invalid length parameter passed to the LEFT or SUBSTRING function.

    Select

    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),7,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))-1)
    Else ''
    End as AttnField,


    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'

    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+2,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+1) -
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet)))

    Else '' --substring for when attention is not in data
    End as Addr1Field

     


    • Edited by David9501 Tuesday, September 11, 2018 7:10 PM
    Tuesday, September 11, 2018 7:09 PM

Answers

  • I'd like just the ones with line breaks.

    Okay, then try using your original SQL and simply add a criteria to it to see if the error goes away. For example:

    SELECT CASE... blah, CASE... blah, etc.
    FROM TableName
    WHERE CHARINDEX(looking for line break here) > 0

    Hope it helps...

    • Marked as answer by David9501 Wednesday, September 12, 2018 1:15 PM
    Tuesday, September 11, 2018 8:54 PM

All replies

  • Hi David,

    Just a guess but do all your records have a line break in them? If some don't have it, it might be causing the error. Try it out on a small record set first where you're sure there are no missing line breaks.

    Hope it helps...

    Tuesday, September 11, 2018 7:15 PM
  • It may be because the delimiter is not consistent.

    Make sure line break is coming consistently in all data

    Alternatively try like below for handling the missing cases

    Select
    
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),7,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))-1)
    Else ''
    End as AttnField,
    
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))+2,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))+1) - 
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))) 
    Else '' --substring for when attention is not in data
    End as Addr1Field


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 11, 2018 7:26 PM
  • Some rows do not have line breaks in them.
    Tuesday, September 11, 2018 7:35 PM
  • Some rows do not have line breaks in them.

    That could definitely cause problems. Either add a criteria to exclude those records or add another case where you skip the records without line breaks.

    Hope it helps...

    Tuesday, September 11, 2018 7:45 PM
  • How would I go about this? Would I add the criteria to the case statement?
    Tuesday, September 11, 2018 7:53 PM
  • How would I go about this? Would I add the criteria to the case statement?

    Hi David,

    What is your complete SQL statement? Do you want to include those records without line breaks in the query result? Have you tried what Visakh posted earlier?

    Since I don't have access to a server right now, so can't test anything, here's a couple of pseudocodes (just to illustrate the idea).

    SELECT CASE blah blah blah
    FROM TableName
    WHERE CHARINDEX('looking for line break here) > 0

    Or, if you want all records returned, then perhaps include the additional criteria in the CASE statements.

    SELECT CASE LEFT(blah)='ATTN:' AND CHARINDEX(blah) > 0 THEN

    Hope it helps...

    Tuesday, September 11, 2018 8:22 PM
  • I have tried Visakh's post but am getting an error message: The data types text and char are incompatible in the add operator.

    Select somast.fsono, soship.fmstreet,

    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),7,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))-1)
    Else ''
    End as AttnField,

    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+2,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+1) -
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet)))
    Else '' --substring for when attention is not in data
    End as Addr1Field

    from soship inner join somast on soship.fcsono=somast.fsono

    --Expression to find difference between LineBreak2 and Linebreak1

    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+1) -
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))

    Tuesday, September 11, 2018 8:36 PM
  • Hi,

    In the two approaches I mentioned above, which one did you need for this situation? Do you want all records or just the ones you're parsing with the line breaks?

    Tuesday, September 11, 2018 8:42 PM
  • I'd like just the ones with line breaks.
    Tuesday, September 11, 2018 8:50 PM
  • I'd like just the ones with line breaks.

    Okay, then try using your original SQL and simply add a criteria to it to see if the error goes away. For example:

    SELECT CASE... blah, CASE... blah, etc.
    FROM TableName
    WHERE CHARINDEX(looking for line break here) > 0

    Hope it helps...

    • Marked as answer by David9501 Wednesday, September 12, 2018 1:15 PM
    Tuesday, September 11, 2018 8:54 PM
  • Hi David9501,

    Per your description,  I think one possibility  is that the issue may be related that your charindex is returning a value of 0, and thus error. For example, if the column A contains a value of 'B', charindex(',', A) would return a value of 0. This means that your SUBSTRING function would attempt to return the left characters whose length is negative value . Of course, that isn't going to work.

     

    By the way , the error is related to data in your table. I inserted two different sample values in my table and then use Visakh16's script. You can see that one shows error message and the other  execute successfully.  Could you please show us table structure and some sample data along with your expected result? So that we’ll get a right direction and make some test.

     

    You  can try following script to see if it satisfies your requirement.

    -----drop table soship
    create table soship
    (fmstreet varchar(60))
    insert into soship values ('ATTN:afbcdvsdnsmsmsshahabfbagb ATTN:')
    
    Select
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),7,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))-1)
    Else ''
    End as AttnField,
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+2,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+1) - 
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))) 
    Else '' --substring for when attention is not in data
    End as Addr1Field
    from soship
    /*
    AttnField                               Addr1Field
    -----------------------------------     -----------------
    fbcdvsdnsmsmsshahabfbagb ATTN:
    */
    
    
    ------------------------------Visakh16-------------------------------------------
    Select
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),7,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))-1)
    Else ''
    End as AttnField,
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))+2,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))+1) - 
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))) 
    Else '' --substring for when attention is not in data
    End as Addr1Field
    from soship
    /*
    Msg 537, Level 16, State 3, Line 26
    Invalid length parameter passed to the LEFT or SUBSTRING function.
    */
    
    delete From soship
    insert into soship values ('ATTN:afbcdvsdnsmsmsshahabfbagb ATTN:
    ATTN:nfkagqkgbqa')
    Select
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),7,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))-1)
    Else ''
    End as AttnField,
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))+2,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))+1) - 
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet + CHAR(13)+CHAR(10)))) 
    Else '' --substring for when attention is not in data
    End as Addr1Field
    from soship
    /*
    AttnField                                                      Addr1Field
    -----------------------------------------              ------------------------
    fbcdvsdnsmsmsshahabfbagb ATTN:
    ATTN                                                             ATTN:nfkagqkgbqa
    */
    


     

    Hope it can help you .

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 12, 2018 7:45 AM
  • Thank you! The where clause solved my issues.
    Wednesday, September 12, 2018 1:15 PM
  • Thank you! The where clause solved my issues.

    Hi David,

    You're welcome! Visakh, Rachel, and myself were all happy to assist. Good luck with your project.

    Wednesday, September 12, 2018 2:44 PM