complicated update replace part of field

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
Wolfgang
I live to help wx-kind
I live to help wx-kind
Posts: 174
Joined: Mon Jan 28, 2019 8:22 am

complicated update replace part of field

Post by Wolfgang » Thu Dec 19, 2019 5:49 pm

I look of a simple resolution (like one update/replace command) for the following problem, or will I have to write a programm.

Inside one field of the database there are " G5625" to " G5944" standing inside the field, which simply must be deleted from the field.

For example if the field consists for example of
That is a sample G5625 G5624 field
it should afterwards look like that:
That is a sample G5624 field
Fieldname is wort
tablename is bibel

utelle
Moderator
Moderator
Posts: 949
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: complicated update replace part of field

Post by utelle » Thu Dec 19, 2019 7:02 pm

Wolfgang wrote:
Thu Dec 19, 2019 5:49 pm
I look of a simple resolution (like one update/replace command) for the following problem, or will I have to write a programm.

Inside one field of the database there are " G5625" to " G5944" standing inside the field, which simply must be deleted from the field.
The task is formulated a bit poorly. Do you have the expressions G5625 and G5944 always side by side separated only by a space character (as in your example below)? Is the order of the expressions relevant? Or is it sufficient to have both expressions somewhere in the field?
Wolfgang wrote:
Thu Dec 19, 2019 5:49 pm
For example if the field consists for example of
That is a sample G5625 G5624 field
it should afterwards look like that:
That is a sample G5624 field
Fieldname is wort
tablename is bibel
One way to do it in SQLite would be:

Code: Select all

UPDATE bibel SET wort = REPLACE(wort, 'G5625', '') WHERE wort LIKE '%G5625 G5624%';
The string expression after LIKE has to be adjusted accordingly, if the expressions are not side by side in the given order.

Wolfgang
I live to help wx-kind
I live to help wx-kind
Posts: 174
Joined: Mon Jan 28, 2019 8:22 am

Re: complicated update replace part of field

Post by Wolfgang » Fri Dec 20, 2019 10:27 am

To make it a bit clearer some more examples:
Example two G5944, not finished yet
The G5944 should not be there.
Example Three G3456 this gets long G5746 really long G2434 really
only the G5746 should not be there.

So any apperance of any number from 5625 to 5944 with a " G" in front of it should be deleted. Everything else should not be changed, and there is no rule that anything else of it is standing close by.

utelle
Moderator
Moderator
Posts: 949
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: complicated update replace part of field

Post by utelle » Fri Dec 20, 2019 2:38 pm

Wolfgang wrote:
Fri Dec 20, 2019 10:27 am
To make it a bit clearer some more examples:
Example two G5944, not finished yet
The G5944 should not be there.
Example Three G3456 this gets long G5746 really long G2434 really
only the G5746 should not be there.

So any apperance of any number from 5625 to 5944 with a " G" in front of it should be deleted. Everything else should not be changed, and there is no rule that anything else of it is standing close by.
Well, unfortunately SQLite does not offer string manipulation functions that would allow you to solve this task with SQL commands. Other database systems like PostgreSQL often offer an extended set of SQL functions, for example for support of regular expressions. You can either extend SQLite by writing appropriate user-defined functions or you could implement a dedicated small application to solve the task.

The wxSQLite3 wxWidgets wrapper implements regular expressions using the wxWidgets implementation. That is, if your application is based on wxWidgets you could use the REGEXP operator / function of SQLite. However, I don't know whether this will be enough to solve your task.

Post Reply