CODEWITHSUNDEEP
sqlsqlite
tmighty
tmighty

Reputation: 11399

Replacing special char in all table

I have some text values in text columns that I would like to change quickly.

The text value in question is %S.

An example for such a text in text column is

You're just downloading %S.

or 

%S has successfully been updated.

I would like to change %S so that the 2 fields would for example read

You're just downloading MySuperApp.

and

MySuperApp has successfully been updated.

The character % itself is an operator in an SQLite search, and I don't know how I could wrap it to be some text value.

Also, I don't know the required SQL command to change only a part of a text in a single SQLite statement. So far, I've only changed entire field texts, not certain parts of it.

Upvotes: 0

Views: 23

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

The character % itself is an operator in an SQLite search, and I don't know how I could wrap it to be some text value.

You could use REPLACE function(there is no need for wrapping it):

WITH cte(t) AS (
   VALUES ('You''re just downloading %S.'),
   ('%S has successfully been updated.')
)
SELECT t, REPLACE(t, '%S', 'MyUserApp') AS replaced
FROM cte;

DBFiddle Demo

Or if you need UPDATE table:

UPDATE tab_name
SET col_name = REPLACE(col_name, '%S', 'specific value')
WHERE col_name LIKE '%^%S%' ESCAPE '^';  -- here you have to escape it

DBFiddle Demo2

Upvotes: 1

Related Questions