MySQL Finding a larger string by knowing only a part of it

I have a string like this:

x26y6z8/0|x999y0z1/1|x1y5z40/9999|etc…

Let’s say I know this:

x1y5z40

How can I find the whole part of the string I’m looking for? Which is

x1y5z40/9999

It is actually simple, but the way I’m doing it is absolutely not the correct one, as I’m spamming the database with queries and doing it all with php, which obviously results in it being slow.

To make things more difficult, once I found

x1y5z40/9999

I need to replace it with, for example:

x1y5z40/0

I would like to do it entirely with MySQL if possible, maybe with 1 query, somebody got any idea on how could I do?

Answer

I think you are leaving out that the components of the string are separated by |. I think you can do this using MySQL string functions. The following should return the second part:

select substring_index(substring_index(col, '|x1y5z40/', 2), '|', 1)

Ah, now to replace the second part with 0. That will be a bit uglier:

select replace(col,
               concat('|x1y5z40/',
                      substring_index(substring_index(col, '|x1y5z40/', 2), '|', 1),
                      '|'
                     ),
               '|x1y5z40/0'
               )

You can also express this as an update, if you are trying to change the data in the database.

By the way, storing lists of things in strings is a very bad idea in SQL. Perhaps you don’t have choice. But SQL has an excellent data structure for storing lists, with all sorts of built-in functionality. It is called a table. You should have a junction table with one row per entity and value in the string.

Leave a Reply

Your email address will not be published. Required fields are marked *