0

I have a mysql table with a row that contains vocabulary, like this:

  • aller to go
  • manger to eat
  • soulager to relieve

There is also a unique column. The problem is that not all of the verbs have the to in the infinitive like above, so in reality the table looks more like this:

  • aller go
  • manger to eat
  • soulager relieve

Is it possible to edit all these lines without the to so that they have it, i. e. so that the second example looks like the first one above? Otherwise I would have to go through the whole thing and edit them manually, which is time consuming as you can imagine.

It's easy to see them all:

SELECT * FROM vocab where french not like "to %";

But edit them?

gview
  • 14,876
  • 3
  • 46
  • 51
AlexM
  • 325
  • 4
  • 11
  • Your query would at very least require `NOT LIKE '% to %` – gview Jun 12 '19 at 17:03
  • 1
    There are a bunch of string functions that could be used to solve this problem, but if it's a one time deal, I'd use [REGEXP_REPLACE] (https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) – gview Jun 12 '19 at 17:08
  • Is `french` the only column and it contains values like `'aller to go'`? – forpas Jun 12 '19 at 17:14
  • It's a bit suspect that both aller and to go are in the same column :-( – Strawberry Jun 12 '19 at 17:20
  • 1
    No, actually they are in different columns, but I failed to make that clear in the question. But it does not matter, because I got the logic that I needed to use from the answers below, so everything all right. – AlexM Jun 12 '19 at 23:14

2 Answers2

2

I think you need an update:

update vocabulary
set french = 
  concat(substring_index(french, ' ', 1), ' to ', substring_index(french, ' ', -1))
where french not like '% to %';

See the demo.
Results:

| french              |
| ------------------- |
| aller to go         |
| manger to eat       |
| soulager to relieve |
forpas
  • 160,666
  • 10
  • 38
  • 76
0

Sure, use update:

UPDATE vocab 
SET french = CONCAT("to ", french)
where french not like "to %";
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
L. Scott Johnson
  • 4,213
  • 2
  • 17
  • 28
  • that only add `to` at the begining. I think Is a little more complex than that – Juan Carlos Oropeza Jun 12 '19 at 17:06
  • That's not how LIKE works, per my first comment. What you change it to won't be as expected either. Perhaps you should test it out in a dbfiddle? – gview Jun 12 '19 at 17:11
  • Yes, I understand what you're saying. It's just a question as to which part of the OP's problem statement is incorrect. If the field begins with a "to " (or should), then this works. – L. Scott Johnson Jun 12 '19 at 17:12
  • I understand, but if you look at the examples provided it is clear that the strings have (or not) 'to' in the middle and that he wants to added. ;) – gview Jun 12 '19 at 17:13
  • Yes. I took that as bad table formatting: that the row shown contains two fields. e.g., "manger" and "to eat" – L. Scott Johnson Jun 12 '19 at 17:14