carriage return
-
I have a field in an application that is text. It will contain one long string of text with carriage returns separating the lines when you view it through the GUI. So for example, the field would look like this:
memo
------------------------------------------------------------
Line 1. Line Denied <carriage return> Line 2. Line Denied <carriage return> Line 3. Line Approved <carriage return>
What I need to do is take that one text field and parse out the data according to the carriage return.
Line 1. Line Denied
Line 2. Line Dendied
Line 3. Approved
Now you have three records as opposed to one. Also on this note, I have noticed that there multiple carriage returns between the separators (for example Line 1. Line Denied <carriage return> <carriage return> Line 2. Line Denied
I know char(13)+char(10) will indentify this but I also need to insert those records with distinctive line numbers
ID Suffix Memo
1 1 Line 1. Line Denied
1 2 Line 2. Line Dendied
1 3 Line 3. Approved
An identity field would work here but I have to hold each unique ID number that might have multiple lines on it. So it would have to loop for those IDs which I guess I would use a cursor to loop it?
Any help would be appreciated. Thanks. Newbie here so take it easy on me
-
You're going to need a loop, and if you have multiple rows, a cursor to fetch through the rows.
For each row, first replace every LF with CR
SET @value = REPLACE(@value, CHAR(10), CHAR(13))
Now eliminate duplicates:
WHILE CHARINDEX(CHAR(13) + CHAR(13), @value) != 0
SET @value = REPLACE(@value, CHAR(13) + CHAR(13), CHAR(13))
Finally set up a loop to extract the values.
SET @i = 1
WHILE 1=1
BEGIN
SET @j-2 = CHARINDEX(CHAR(13), @value, @i)
IF @j-2 = 0 BREAK
INSERT @table ([Line]), VALUES (SUBSTRING(@value, @i, @j-2 - @i))
SET @i = @j-2 + 1
END
I know that you asked us to be gentle, but if you designed this table, then you need to read up on normalization and normal forms, specifically first normal form: every row contains exactly one value for each column.
Brian
MCDBA, MCSE+I, Master CNE
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply