I understand the concept of ‘find and replace’ is cutting edge and only been around a few months, but is it possible to include one in this program?
I have tried using queries but none of the ‘solutions’ here have worked. Always get 0 results.
Beyond frustrated.
Most used: Find data in a database.
There are many people, who would prefer parameter queries. I always use the solution with a table (“tbl_filter”), which only save one row for the value/text I am looking for.
Then I create a query which should show the values, which are like the values in this “tbl_filter”.
Both (input for “tbl_filter” and query) are parts of one form. So with every new value in “tbl_filter” I could see all content of the query in the form directly. This content I could change now.
But don’t know what you are trying to replace. If you want to replace content very often it might be your database construction isn’t the right construction. I won’t need this in any database here. Have only needed it in the past for pupils and classes in scool:
UPDATE "tbl_pupils" SET "class" = "class" + 1
Will be send every new year to get the new classes.
I don’t understand why anybody needs to perform a search&replace when using a well formed database where every string is stored only once. Access that string, edit the string, and it will appear modified in every context.
Yes, a query helps to find the string to be edited.
Queries store SELECT statements. Anything that is not a SELECT statement (DELETE, INSERT, UPDATE) is called an “action query” in MS Access as far as I remember.
In Base you select the records of interest by means of queries used in forms. Forms allow to select many records from many interrelated tables, so you can edit many tables on the same form.
When you store a modified a record, Base performs an UPDATE command.
When you add a new record, Base performs an INSERT command.
Finally, you can DELETE records on forms.
If you want to run “action queries” (DELETE, INSERT, UPDATE) beyond any user interface, call menu:Tools>SQL… and communicate with the underlying database engine directly.
… and with the current set of informations given from you nobody can help you to solve your problem.
.
Type of database/
what is to be searched/
Where to search/
Result to be replaced with…
.
Only advice for the availabe info:
DUMP your data and use the one of the tools for text conversion like awk, perl etc. In smaller databases you may even use Calc, if you don’t manage this in SQL. .
Base is no database itself. It connects LibreOffice to different databases.
.
PS: Where is your mentioned “here”? Can you give us some links?
If I were to follow this advice, it would take 1 minute multiplied by about 500,000 so it would take just under 1 year to complete if I worked 24 hours per day 7 days per week.
Didn’t we invent computers to PREVENT that sort of thing happening?
Thankyou for replying but I just accomplished the task by using that unnecessary function in calc: “find and replace”
I think you are right that I needed to change a SELECT to an UPDATE in the query - but its done now (thanks to a find and replace function)
I was not requesting a solution to my problem, I was requesting a feature so the solution didn’t involve exporting into a program that does have find and replace.
For the record, the task was (supposed to be) extremely simple.
Truncate trailing spaces that came from a csv import.
The closest answer here was to use:
SELECT “Name”, “newName”,LENGTH(“newName”) FROM
(SELECT “Name”, RTRIM(“Name”) AS “newName” FROM “List”)
but that just gives a table with the rights answers - but it cannot be copied / pasted
My SQL is a bit rusty but something like
UPDATE "TABLENAME" SET "Name" = RTRIM("Name")
should remove trailing spaces from the data in field Name
Have a look here:
Bug 32506
Bug 119093
Discussed there, isn’t been implemented and might be never implemented, because databases aren’t the same as tables in Calc.
We? - Sorry I’m not that old. But actually command-line interfaces and SQL were invented to handle files/databases far to big to be loaded in your computers memory.
.
After having more memory we got more friendly approaches to handle files than sed. But stll: In the world of databases “we” often handle files to big to load and maybe not even on my own computer.
.
@Villeroy did not suggest you should edit every line with a specific command but to use one update-statement as written by @robleyd
But this commands can not be written in the query-window.
UPDATE "List" SET "Name"="RTRIM("Name") as suggested by @robleyd, solves your problem. RTRIM(“Name”) does nothing when there is no trailing space, so this command can be allowed to run unconditionally across the whole record set.
UPDATE "List" SET "Name"="REPLACE("Name", 'ou', 'ow') would certainly give unwanted changes in a large list of person names. Therefore, one would filter names containing ‘ow’ and if that filter returns too many names, refine the filter criteria until you get a filtered list small enough to replace ‘ou’ with ‘ow’ manually to correct some wrongly writtten/imported names. Having a list of names in a filtered form where all occurrences of some string need to be replaced with another string, could be a task for a macro programmer.