[Solved] Replace string within field

[Doranwen]

Once again, I've been working on my fanfic database, which is a split database with HSQLDB 2.5.1 as the backend, LibreOffice Base as the frontend. It has a table called Fanfics, with the primary key FanficID, and varchar fields like Title and OfflineLink (among many others, most of which are not relevant to this issue).

I'd like to replace a specific string in OfflineLink with another string, say replacing "Other Media Sections/The Matrix" with "The Matrix Section". Neither string is the entire contents of the field in ANY record. (That field is unique to each record in the database, but much of it does overlap between large portions of records, and it's one of the portions that overlaps that I want to replace - about 700 records so far that need that replaced in.)

So I read up on the REPLACE function in SQL, and came up with the following SQL code:

Code: Select all

UPDATE "Fanfics" SET "OfflineLink" = REPLACE("OfflineLink", 'Other Media Sections/The Matrix', 'The Matrix Section')

I tested it by setting the condition WHERE "FanficID" = 1446, because I figured if it went wrong, I'd only have one record to redo that field for. The problem is… it didn't do anything! It said it successfully executed the command, but absolutely nothing happened, as far as I can tell. That record still has the original string in it.

Is there a way to replace this string within the field without needing it to be the entire contents of the field? I definitely don't want to have to manually edit 700 records! Did I use the wrong syntax? (It can be tricky adapting a command to Base's SQL requirements.) Did I miss an obvious guide online that would've solved my problem? Any assistance will be greatly welcomed.

[chrisb]

your UPDATE statement works as expected with HSQLDB 2.5.1, libreoffice 6.4.4.2 (x64) & windows 10.

ensure that the search string is accurate by executing:

Code: Select all

UPDATE "Fanfics" SET "OfflineLink" =
REPLACE("OfflineLink", 'Other', 'BROTHER')
where "FanficID" = 1446

[Doranwen]

*headdesk* it was working fine, I just didn't realize the form *wouldn't show it* without closing it and re-opening it. I thought if I just went next/previous with the records, that it would grab the new version. But it did not, and I didn't have a refresh button for that particular part of the form because it's part of the main table (Fanfics) rather than a secondary table which might be modified separately. (I rarely modify anything within the Fanfics table once initially entered.) Closing and opening the form (which I'd have probably tried had I been thinking straight) cleared it. (I don't try to scroll that far down in a table directly so I do all my work with the forms and SQL.)

Thanks for confirming that it should work, as that made me think of closing/reopening the form. Usually I suspect the issue is with my SQL, LOL. But this case it looks like I did read it all correctly.