CODEWITHSUNDEEP
sqlite
Josef H.
Josef H.

Reputation: 19

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

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

Upvotes: 0

Views: 219

Answers (1)

forpas
forpas

Reputation: 164099

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 '); "'.

Upvotes: 1

Related Questions