Avatar of maximus1974
 asked on

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.


Do not buy this PN

Note: FMV: $12588
Oracle DatabaseSQL* oracle syntax

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
Bill Prew

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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...

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.