October 21, 2008 at 3:46 pm
I have a column that stores 3 lines (was typed in with 2 carriage returns) continuously.
I use the below statemnt and was able to get out the first line
SELECT line1 = substring(TXTFIELD,1,charindex(CHAR(13),TXTFIELD))
FROM SY03900 where NOTEINDX='1431.00000'
How do I get the other lines separately? I guess I should put how I could skip the first charindex(13) to get the second text string and stop before the last char(13) and then get the third text string?
TIA.
October 21, 2008 at 4:21 pm
charindex can take a third parameter, the location at which to start. Use the charindex from the first one as the starting point.
SELECT line1 = substring(TXTFIELD,1,charindex(CHAR(13),TXTFIELD))
, line2 = substring(TXTFIELD,charindex(CHAR(13),TXTFIELD), charindex(CHAR(13),TXTFIELD) + 1)
October 21, 2008 at 4:22 pm
CHARINDEX can take an optional starting position and you can use that to tell it where to look for the next "token" - CHARINDEX(CHAR(13), mytextfield, PositionOfThePreviousCR).
Note - you might have both a Carriage return (13) and a linefeed (10) next to each other, and you'll probably want to remove both from the result.
October 21, 2008 at 4:22 pm
Sorry, you can also use the 2nd charindex to determine where to stop the first line in the substring function
October 21, 2008 at 9:06 pm
Steve Jones - Editor (10/21/2008)
charindex can take a third parameter, the location at which to start. Use the charindex from the first one as the starting point.SELECT line1 = substring(TXTFIELD,1,charindex(CHAR(13),TXTFIELD))
, line2 = substring(TXTFIELD,charindex(CHAR(13),TXTFIELD), charindex(CHAR(13),TXTFIELD) + 1)
hmm, this is the result for line2
This is the second line. This is the thr
As you could see, I really need it to return This is the second line. Tips?
Also, I manage to get the third line with the below statement
select line3 = substring(TXTFIELD,charindex(CHAR(13),TXTFIELD,charindex(CHAR(13),TXTFIELD)+1), charindex(CHAR(13),TXTFIELD) + 1)
from sy03900 where noteindx='1431.00000'
Thank you all for the replies
October 21, 2008 at 10:14 pm
I'll take a shot at it. This is just a sample code:
Run this code:
declare @mytable table (mystring varchar(1000))
insert into @mytable values( 'This is the first line ' + CHAR(13) + 'This is the second line' + char(13) + 'This is the third line')
select
'1st line: ' = SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)-1),
'2nd line: ' = SUBSTRING(SUBSTRING(myString, len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)+1)),LEN(myString)) , 1, charindex(char(13),SUBSTRING(myString, len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)+1)),LEN(myString)) ,0)-1),
'3rd line: ' = rtrim(substring(myString,len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)-1) + SUBSTRING(SUBSTRING(myString, len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)+1)),LEN(myString)) , 1, charindex(char(13),SUBSTRING(myString, len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)+1)),LEN(myString)) ,0)-1))+ 3,len(mystring)))
from @mytable
October 21, 2008 at 10:22 pm
You might also check to see if it is really a char(13) that you have between those two characters and not a space or tab or char(10). ASCII() will tell you the ascii value for the character, but you'll need to use SUBSTRING to isolate it since it only works on the leftmost character in a string (e.g. ASCII(SUBSTRING(...))).
Chad
October 22, 2008 at 11:50 am
ck9663 (10/21/2008)
I'll take a shot at it. This is just a sample code:Run this code:
declare @mytable table (mystring varchar(1000))
insert into @mytable values( 'This is the first line ' + CHAR(13) + 'This is the second line' + char(13) + 'This is the third line')
select
'1st line: ' = SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)-1),
'2nd line: ' = SUBSTRING(SUBSTRING(myString, len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)+1)),LEN(myString)) , 1, charindex(char(13),SUBSTRING(myString, len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)+1)),LEN(myString)) ,0)-1),
'3rd line: ' = rtrim(substring(myString,len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)-1) + SUBSTRING(SUBSTRING(myString, len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)+1)),LEN(myString)) , 1, charindex(char(13),SUBSTRING(myString, len(SUBSTRING(myString, 1, CHARINDEX(CHAR(13),myString,0)+1)),LEN(myString)) ,0)-1))+ 3,len(mystring)))
from @mytable
Do you know how I could correct this then? Thanks
Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of len function.
I know in your example, it works. However in my case, I replace myString with the column TXTFIELD in my table. TXTFIELD is text. Any tips?
October 22, 2008 at 6:24 pm
NM. Google has it. Replace len with datalength.
I don't think I could ever come up with anything like this, espcially from an accountant :hehe:
Thanks all who replied.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply