Splitting value in MySQL

Question

I want to update a field on a really huge (1m rows) table. I want to update it from:

+-----------------------------------------------------------+
|                            ref                            |
+-----------------------------------------------------------+
| 0001___000000000003616655___IVANTI UK___TEMPLATE MATERIAL |
+-----------------------------------------------------------+

to:

+-------------------------------+
|              ref              |
+-------------------------------+
| IVANTI UK___TEMPLATE MATERIAL |
+-------------------------------+

So basically its just changing the ref (which is not fixed length) from sid___sku___mfr___pnum to mfr___pnum format.

In PHP I'd do it like so (pseduo code):

list($p['sid'], $p['sku'], $p['mfr'], $p['pnum']) = explode('___', $row['ref']);
$row['ref'] = $p['mfr'] . '___' . $p['pnum'];

Wondering if its possible to do it directly with MySQL with a performant query?

Something with SUBSTRING_INDEX/LOCATE/SUBSTRING, please read [ask] and show what you have tried. If the `sid___sku___` has a predetermined length it becomes even more trivial. — CodeCaster, Jul 23 '17 at 12:09
Seems like you're trying to do the reverse (opposite) of your previous question https://stackoverflow.com/q/45263168/1415724 am I right? — Funk Forty Niner, Jul 23 '17 at 12:11
@Fred-ii- In a way, but that table has the mfr and pnum, all the other tables don't and I don't want to do a join — eozzy, Jul 23 '17 at 12:12
is this because some need to be concatenated while others don't? If so, you could problably use a CASE for this and/or a ternary. — Funk Forty Niner, Jul 23 '17 at 12:12
Possible duplicate of [How to split the name string in mysql?](https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql) — CodeCaster, Jul 23 '17 at 12:13
Not my dv here btw. Wondering why you're getting such a negative response. — Funk Forty Niner, Jul 23 '17 at 12:13
@Akira, Nashir, the problem is very clear. It's just that this is about their 700th question and they still don't know Stack Overflow isn't a code writing service. You're required to show your research. — CodeCaster, Jul 23 '17 at 12:16
@CodeCaster Probably because you're new to something and you don't even know where to start? With PHP or JS I would provide snippets, but I know nothing about MySQL — eozzy, Jul 23 '17 at 12:18
I can recommend Google, it's a great start. The question I chose as duplicate was the first hit for "mysql split string", which I'm sure you've tried researching, yet none of that code is in your question. Showing failed attempts is very useful as well. But thanks for reminding me why to stay out of the php and mysql tags. — CodeCaster, Jul 23 '17 at 12:20
@CodeCaster Do you begin learning a whole new language/technology if you're stuck on something or just ask for help on SO? — eozzy, Jul 23 '17 at 12:21
Yeah why invest time in bettering yourself while you can let others invest theirs? — CodeCaster, Jul 23 '17 at 12:22
@CodeCaster I did better myself by learning from each question, but I won't read the whole manual if I'm stuck on one thing. — eozzy, Jul 23 '17 at 12:23
Nobody asked you to read the whole manual. I asked you what you _have_ tried though, but apparently not much. Next time try searching first. — CodeCaster, Jul 23 '17 at 12:24
Possible duplicate of [Is there a way to find a last substring?](https://stackoverflow.com/questions/17226668/is-there-a-way-to-find-a-last-substring) — mickmackusa, Jul 23 '17 at 13:16

score 2 · Accepted Answer · answered Jul 23 '17 at 12:16

select SUBSTRING_INDEX(ref,'___',-2) from test

0001___000000000003616655___IVANTI UK___TEMPLATE MATERIAL
=>
IVANTI UK___TEMPLATE MATERIAL

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

Splitting value in MySQL

1 Answers1