[Solved] Find and replace substring in a table field

Creating tables and queries
Post Reply
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

[Solved] Find and replace substring in a table field

Post by Nocton »

I would like to use an SQL Query to find a substring in a table field, but can't seem to get anything to work. I have:
Table: 'Matches'
Fields: 'Name1', 'Name2', 'Name3'

Each field consists of names in the format 'Firstname LastName'

I would like to search all fields for "OldName" and if found replace all with "NewName".
Perhaps to make it simpler, OldName could be specified as being a FirstName or a LastName
Last edited by Nocton on Wed Sep 13, 2017 9:38 am, edited 1 time in total.
OpenOffice 4.1.12 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Find and replace substring in a table field

Post by Villeroy »

there is a REPLACE function in http://www.hsqldb.org/doc/1.8/guide/ch09.html#N1251E
SELECT REPLACE('OldName', "Name Field1", 'NewName') AS "F1",
REPLACE('OldName', "Name Field2", 'NewName') AS "F2",
REPLACE('OldName', "Name Field3", 'NewName') AS "F3"
FROM "Table"
WHERE "Name Field1" LIKE '%OldName%' OR "Name Field2" LIKE '%OldName%' OR "Name Field1" LIKE '%OldName%'
If you get this selection right, you can run a DML statement (data manipulation language) in Tools>SQL to actually mass edit the table values

Code: Select all

UPDATE "Table" SET "Name Field1" = REPLACE('OldName', "Name Field1", 'NewName');
UPDATE "Table" SET "Name Field2" = REPLACE('OldName', "Name Field2", 'NewName');
UPDATE "Table" SET "Name Field3" = REPLACE('OldName', "Name Field3", 'NewName');
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Find and replace substring in a table field

Post by Nocton »

Thank you, Villeroy.

The second, DML statement, option works perfectly, although rather slow. However, I find that the syntax should be:

Code: Select all

UPDATE "Table" SET "Name Field1" = REPLACE("Name Field1", 'OldName', 'NewName');
UPDATE "Table" SET "Name Field2" = REPLACE("Name Field2", 'OldName', 'NewName');
UPDATE "Table" SET "Name Field3" = REPLACE("Name Field3", 'OldName', 'NewName');
The first SQL statement similarly has the syntax the wrong way round, but also works OK as a Query. This option would allow me to enter the OldName and NewName as parameters to be set at run time. However in this case the output consists of only the records where the changes were made and does not actually update the table. I think it is not possible to add an UPDATE statement to actually make the required changes? UPDATES have to be done via Tools>SQL?
OpenOffice 4.1.12 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Find and replace substring in a table field

Post by Villeroy »

Yes, I misread the documentation.
Yes, everything that does not start with SELECT has to be done in the SQL window.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply