String split in MySQL using REPLACE and SUBSTRING_INDEX

Question

I need to split this:

a:6:{s:8:"address1";s:11:"2 Bourke St";s:8:"address2";s:13:"Woolloomooloo";s:4:"city";s:6:"Sydney";s:5:"state";s:3:"NSW";s:11:"postal_code";s:4:"2011";s:7:"country";s:9:"Australia";}

by ; to pull out the value 2011 found near the end of the string. Even pulling out s:4:"2011" would work.

I am currently using this code:

REPLACE(SUBSTRING_INDEX(address, ';', 10), LENGTH(SUBSTRING_INDEX(address, ';', 10 -1)) + 1), ';', '')

I got it from a site that was listing it as a solution for splitting strings in MySQL... but it has syntax errors in it. I tried debugging it and found that there is one too many ) but I was unable to figure out how to change it to get it to work... I'm lacking understanding of how this is supposed to split strings.

It seems overly complicated compared to this solution...

Can anyone provide assistance? An explanation of how it works would be great or an alternative solution would work as well.

Thanks!

mathematical.coffee · Accepted Answer · 2012-03-21 23:59:49Z

Read the documentation for SUBSTRING_INDEX and REPLACE and it'll be clearer what the code is trying to do.

Note that SUBSTRING_INDEX(address,';',10) retrieves all of address up to the 10th occurence of ;, so in your case from the start of the string up to the s:4:"2011".

REPLACE(string,from,to) replaces all occurences of from with to. It looks like the code you tried is attempting to replace everything up to the 9th ';' with nothing (via the REPLACE), and also cut off everything after the 10th ';'. This would leave the 's:4:"2011"'.

One way you could extract s:4:"2011" is to use SUBSTRING_INDEX(address,';',-4) which returns everything from the 4th-last ; onwards. In your case, that would be s:4:"2011";s:7:"country";s:9:"Australia";}.

Then, use SUBSTRING_INDEX(...,';',1) on this resulting string to get everything before the 1st occurence of ;, being s:4:"2011".

In summary:

SUBSTRING_INDEX(SUBSTRING_INDEX(address,';',-4),';',1)

I'm sure there are other ways to do it too.

user1191247 · Accepted Answer · 2012-03-21 23:56:32Z

0

This should do it -

SET @a := 'a:6:{s:8:"address1";s:11:"2 Bourke St";s:8:"address2";s:13:"Woolloomooloo";s:4:"city";s:6:"Sydney";s:5:"state";s:3:"NSW";s:11:"postal_code";s:4:"2011";s:7:"country";s:9:"Australia";}';
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@a, ';', -4), ';', 1);

answered Mar 21, 2012 at 23:56

user1191247

13k2 gold badges25 silver badges33 bronze badges

Collectives™ on Stack Overflow

String split in MySQL using REPLACE and SUBSTRING_INDEX

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related