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

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

 

Answer 1

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...

Answer 2

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

Answer 3

Some rows do not have line breaks in them.

Answer 4

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...

Answer 5

How would I go about this? Would I add the criteria to the case statement?

Answer 6

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...

Answer 7

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))

Answer 8

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?

Answer 9

I'd like just the ones with line breaks.

Answer 10

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...

Answer 11

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 [e-mail].

Answer 12

Thank you! The where clause solved my issues.

Answer 13

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.