CODEWITHSUNDEEP
mysqlsqlsubstringsplit
Matt
Matt

Reputation: 5595

String split in MySQL using REPLACE and SUBSTRING_INDEX

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!

Upvotes: 1

Views: 9889

Answers (2)

mathematical.coffee
mathematical.coffee

Reputation: 56915

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.

Upvotes: 3

user1191247
user1191247

Reputation: 12998

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);

Upvotes: 0

Related Questions