What is the SQLite query to replace a string of characters with another string that includes a line break?

Question

I'd like to replace ); " in column "Note" with )Carriage Return", but with the condition that "Phrase" column (BOOL) is set as TRUE. I tried using the following query but that didn't work:

SELECT 
  * 
FROM "Slovník";
UPDATE Slovník
WHERE   Phrase = 1
SET Note = replace(Note, '); "', ')CHAR(13)"');

In other words, I'd like to replace a semicolon with a line break in the column Note in my database under the above conditions: [screenshot][1]

To avoid the semicolon being replaced somewhere else, (somewhere other than between the sentence examples), I have to include the extra characters, such as ); "

(Please let me know if my question isn't clear enough and I'll try to clarify.)

forpas · Accepted Answer · 2019-11-19 14:42:41Z

1

Use the concatenation operator || with the function CHAR() and don't wrap the function inside the single quotes:

UPDATE [Slovník]
SET Note = replace(Note, '); "', ')' || CHAR(13) || '"')
WHERE   Phrase = 1

It's better to use this WHERE clause:

WHERE   Phrase = 1 AND Note LIKE '%); "%'

to avoid unnecessary updates for the rows that do not contain '); "'.

edited Nov 19, 2019 at 14:42

answered Nov 19, 2019 at 14:01

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josef H. Over a year ago

Thank you for you reply, unfortunately that didn't work for me either. I get a syntax error.

Collectives™ on Stack Overflow

What is the SQLite query to replace a string of characters with another string that includes a line break?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related