Update - let's look at the second field and I get results just fine... I still get the error of the first.. I need to figure out the first.. I think what I need to is to remove the first..
This works.. Second length
SELECT SUBSTRING(AFIELD,
CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD)+1),
CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD)+1)+1) - CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD)+1)) as [second_length]
FROM SOMETABLE with (nolock)
Third length which also works..
SELECT SUBSTRING(AFIELD,
CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD)+1),
CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD)+1)+1)-CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD)+1)) as [THIRD_length]
FROM SOMETABLE with (nolock)
This is still not working.. Hmmm..
SELECT SUBSTRING(AFIELD,
CHARINDEX(CHAR(10), AFIELD),
CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD)+1)-CHARINDEX(CHAR(10), AFIELD)) as [FIRST_length]
FROM SOMETABLE with (nolock)
Old info.. This works fine..
SELECT CHARINDEX(CHAR(10), AFIELD)[first]
,CHARINDEX(CHAR(10), AFIELD,CHARINDEX(CHAR(10), AFIELD)+1)[second]
FROM SOMETABLE
But when I try to subtract the two, the results are empty..
I have followed the information posted here and at - http://phelabaum.com/archive/tag/charindex/
SELECT SUBSTRING(AFIELD,
CHARINDEX(CHAR(10),AFIELD)+1,
CHARINDEX(CHAR(10),AFIELD,CHARINDEX(CHAR(10),AFIELD)+1) - CHARINDEX(CHAR(10),AFIELD)) as [first_length]
FROM SOMETABLE
When I run this, I get: the message:
Msg 537, Level 16, State 3, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.
The field is CHAR(10) or Line Feed delimited.
It works fine if text you're testing indeed has 2 CHAR(10) in it with some text in between. If at least one is missing - you will get that error.
Note that CHARINDEX by itself will not return any errors if char is not found - it will simple return 0, this is why your first query runs.