sqlsqlite
Is it possible using SQL in an SQLite table to replace part of a string?
For example, I have a table where one of the fields holds the path to a file.
Is it possible to replace parts of the string so that, e.g.
c:\afolder\afilename.bmp
becomes
c:\anewfolder\afilename.bmp
?
Try:
INSERT INTO table1 ( column1 )
SELECT col1
FROM table2
This is standard ANSI SQL and should work on any DBMS
It definitely works for:
Assuming three columns in the table: ID, NAME, ROLE
BAD: This will insert or replace all columns with new values for ID=1:
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES (1, 'John Foo', 'CEO');
BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:
INSERT OR REPLACE INTO Employee (id, role)
VALUES (1, 'code monkey');
GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!
UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.
GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).
INSERT OR REPLACE INTO Employee (id, role, name)
VALUES ( 1,
'code monkey',
(SELECT name FROM Employee WHERE id = 1)
);
This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES ( 1,
'Susan Bar',
COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
);
Best Solution
You can use the built in
replace()function to perform a string replace in a query.Other string manipulation functions (and more) are detailed in the SQLite core functions list
The following should point you in the right direction.
UPDATE table SET field = replace( field, 'C:\afolder\', 'C:\anewfolder\' ) WHERE field LIKE 'C:\afolder\%';