MYSQL Extracting data from column result using multiple delimeters

Question

I have a string in a column in mysql as follows:

761696495 - 689552180|723146573 - 684706476|295453556 - 656883782

Is there a way that I can check against that string, for example lets say I want to look either for 723146573 or 684706476, which is the middle item in the delimited piped string, I want to be able to return the whole portion of that string if a match is made. So for example if I wanted to check against 684706476, it would return:

723146573 - 684706476

And as the reverse, if I want to match 723146573, it would return the full match in that position too

723146573 - 684706476

Also just to not make any confusion, essentially I want to be able to match against this whole string for any position of it and if a hit is made it returns the first part of the - and last in that position it is found, hopefully that makes sense.

Are you stuck with this format? It would be much better to normalize your table. — Barmar
– Barmar, Commented Apr 18, 2024 at 1:03
At the moment yes :( So this is why I'm trying to extract from it ideally. I've tried using various versions of substring etc.. but I can't seem to figure out with this method how to do it. — Shane
– Shane, Commented Apr 18, 2024 at 1:04
Use a regular expression to match the number, e.g. [:<:]723146573[:>:]. Then write a stored function that takes the whole column and the search string, loops over the pipe-delimited substrings, and returns the substring that contains the seach string. — Barmar
– Barmar, Commented Apr 18, 2024 at 1:07
See stackoverflow.com/questions/7737970/… for how to loop over a delimited list. — Barmar
– Barmar, Commented Apr 18, 2024 at 1:09

Shaiming Pai · Accepted Answer · 2024-04-18 01:03:44Z

0

Try it! If u use only MySQL, maybe following query is helpful. SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, '|', position), '|', -1) AS match FROM table_name WHERE LOCATE('684706476', column_name) > 0;

answered Apr 18, 2024 at 1:03

Shaiming Pai

13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL Extracting data from column result using multiple delimeters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related