We help IT Professionals succeed at work.

Need to add data with a carriage return or line feed without removing existing data

maximus1974
maximus1974 asked
on
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.

Ex.

Do not buy this PN

Note: FMV: $12588
Comment
Watch Question

View Solution Only

Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
Try this:

UPDATE MyTable SET MyColumn = MyColumn || Chr(10) || 'Note: FMV: $12588';

Open in new window

»bp
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

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

»bp