Need to add data with a carriage return or line feed without removing existing data
I need to update or insert data into a field without removing the existing data that is already in the field. Need to add the data after a line feed or carriage return. The example below shows what I wish to do. Add Note: FMV: $12588 after any existing data already there.
If there's a chance that column values may not contain a linefeed, I would add a where clause to Bill Prew's solution -
where instr(mycolumn,chr(10)) > 0
Bill Prew
Agreed, I assumed, but should have mentioned, that if you didn't want this on every row (wasn't specified in question) then you would/should add any needed WHERE conditions to the query.
However I don't think the author only wanted this applied to rows where the column already contained a Chr(10), they just wanted to append to whatever was already there leaving at least one line feed before the new added text (might want multiple Chr(10)'s if they want one or more blank lines after existing, before new text.
But I know sometimes I misinterpret the need, so as always, adjust suggested solutions as needed, or come back with more refined needs / questions.
If it were me, I'd probably also add a WHERE clause to not apply this to any rows where the column already ends in the new text you want to add. Just in case you have to run it against the same table more than once, etc. Just as a fail safe...
where instr(mycolumn,chr(10)) > 0