June 16, 2003 at 3:08 pm
Is there a way to identify a carriage return in a field and create a new row each for it. For example, if a line had two carriage returns, I would create three lines from that one field into a separate table. Sorry if this are just newbie questions.
June 16, 2003 at 4:53 pm
Use string functions (see BOL) for that. For example, CHARINDEX will return the first occurance if a character in a string. User CHAR(13) or CHAR(10) to identify the carrage return. You would need a loop to find all such characters in a string. Each time one is found use the SUBSTRING function to get te result. You may also need LEFT, RIGHT, LEN and other functions.
June 17, 2003 at 7:43 am
How would I use the substring command in a case like this ..
if carriage return found
substring(column_name,?,?)
June 17, 2003 at 8:17 am
Logic like this :
DECLARE @strToCheck varchar(100)
DECLARE @iPos int
SELECT @strToCheck = column_name FROM ...
WHILE LEN(@strToCheck) > 0
SET @iPos = CHARINDEX(CHR(10), @strToCheck)
--Account for a not found
IF @iPos = 0 THEN
SET @iPos = LEN(@strToCheck) + 1
--Do the insert here
INSERT INTO table (column_name)
VALUES (LEFT(@strToCheck, @iPos-1))
--Strip the things to end the WHILE
SET @strToCheck = RIGHT(@strToCheck, LEN(@strToCheck) - @iPos + 1)
WEND
You can 'simplify' this, by using substrings instead of assigning the 'remaining' part of the string to @strToCheck...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply