0

I am working with a program that allows me to execute SQL after downloading an image. The program also provides tokens which give a specific value depending on the image downloaded. To do this it provides a form field which I have filled with:

INSERT INTO tag_index (tag, index_count) VALUES ("%type%:%tag%", 1) ON DUPLICATE KEY UPDATE index_count = index_count + 1;

I would expect this to insert "location:italy" into tag_index.

However, the values tokens return are surrounded by single quotes. Going by the previous example "'location':'italy'" gets inserted into tag_index instead.

Having these single quotes in tag_index is incompatible with the gallery application I am using, and I cannot edit the application I am downloading the images with to make the tokens not surrounded by single quotes. I need to be able to write a line of SQL that can fit in the downloader applications settings field that removes just the outer single quotes when inserting into tag_index.

For example, would it be possible to do something like:

INSERT INTO tag_index(tag, index_count) VALUES ("ESCAPEOUTERQUOTES(%type%):ESCAPEOUTERQUOTES(%tag%)", 1)
  ON DUPLICATE KEY UPDATE index_count = index_count + 1;

I am using MySQL 5.7.23.

Edit

As suggested by some comments, I do not think using REPLACE would answer my question. A %tag% token can include valid single quotes. For example, when a tag resembles a face ''o'' I need to only remove the outer single quotes leaving 'o'.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Settings Menu Hard to Find
  • 1,121
  • 14
  • 31
  • which database you are using? In my `REPLACE(str, find_string, replace_with)` – Digvijay S Mar 10 '20 at 05:27
  • previous discussion --> https://stackoverflow.com/questions/39052120/how-to-remove-a-single-quote-from-a-value-while-inserting-in-sql-database – Digvijay S Mar 10 '20 at 05:28
  • Does this answer your question? [How to remove a single quote from a value while inserting in SQL database](https://stackoverflow.com/questions/39052120/how-to-remove-a-single-quote-from-a-value-while-inserting-in-sql-database) – Digvijay S Mar 10 '20 at 05:28
  • I don't believe so. A `%tag%` token can include valid single quotes. For example, when a tag resembles a face `''o''` I only want to remove the outer single quotes leaving `'o'`. – Settings Menu Hard to Find Mar 10 '20 at 06:33
  • @BoneJones . . . If you cannot simply use `REPLACE()`, then you need much more explanation on what valid replacement are. – Gordon Linoff Mar 10 '20 at 11:43
  • A valid replacement would include matching the outer single quotes for each token and removing them. I don't believe you can use regex in `REPLACE()` but maybe I am wrong? – Settings Menu Hard to Find Mar 11 '20 at 00:55

0 Answers0