php explode like function in mysql

Question

I have a column in my database name location which have city name and country name in this format New york,America. I want to run a select query with the explode function like which we use in php to separate values like this so that i can get comma , separated values

select explode(",",location) from address;

Also with the alias of city column holding New York and alias of country holding value of America. So that i can use them in my store procedure and insert this values in references table in the columns city and country

Separate? Or join? Your value is joined in the table already unless I am reading this wrong ("I have a column"). — Jay Blanchard, Jun 03 '15 at 11:32
I'm thinking you are looking for the explode variant of PHP. Not implode — DarkBee, Jun 03 '15 at 11:33
You want to separate it *into what*?! MySQL doesn't have arrays, so what would the expected result be? — deceze, Jun 03 '15 at 11:35
@deceze i want them to use in store procedure so that i can store these value in other table — Muhammad Usama Mashkoor, Jun 03 '15 at 11:38
Update your question to clarify that; be specific about what you're trying to do. — deceze, Jun 03 '15 at 11:41
http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — vp_arth, Jun 03 '15 at 11:54
@vp_arth this the solution for the SqlServer i want this for mysql — Muhammad Usama Mashkoor, Jun 03 '15 at 11:58
are you developer? can't you write your own use this logic?... See my answer. — vp_arth, Jun 03 '15 at 12:01

score 4 · Accepted Answer · answered Jun 03 '15 at 11:33

4

You can not really "explode" or split on all comma's, but you can split a string on any comma using SUBSTRING_INDEX.

SELECT SUBSTRING_INDEX('New york,America', ',', 1);
-> New york

answered Jun 03 '15 at 11:33

Sjoerd

74,049
16
131
175

I want to split Colum values not a string @Sjoerd – Muhammad Usama Mashkoor Jun 03 '15 at 11:39
1

@usama, what difference? it's just sql function. – vp_arth Jun 03 '15 at 11:50
@vp_arth the difference is i want to select city,country from location colum and store this into the other table colums city and country using store procedure – Muhammad Usama Mashkoor Jun 03 '15 at 11:52
no any difference in your example) just use `substring_index` function 2 times, for city and for country. – vp_arth Jun 03 '15 at 11:54
@Sjoerd with i can get the New york but witj index 2 SUBSTRING_INDEX('New york,America', ',', 2) it return both New york,America – Muhammad Usama Mashkoor Jun 03 '15 at 12:06
2

try `-1` index instead of `2`. It will mean: `all, after last comma` – vp_arth Jun 03 '15 at 12:08

score 1 · Answer 2 · answered Jun 03 '15 at 11:34

1

Use Group concat

   SELECT GROUP_CONCAT( location )
    FROM `address`

answered Jun 03 '15 at 11:34

Suraj

363
2
16

Read the comments under the question. – Jay Blanchard Jun 03 '15 at 11:36
Sorry, i didn't see the comments. – Suraj Jun 03 '15 at 11:41
@Suraj can you help me what will be the solution – Muhammad Usama Mashkoor Jun 03 '15 at 11:53

vp_arth · Answer 3 · 2015-06-03T12:04:16.023

1

CREATE FUNCTION strSplit(x varchar(255), delim varchar(12), pos int)  
returns varchar(255)
return replace(substring(
  substring_index(x, delim, pos+1), 
  length(substring_index(x, delim, pos)) + 1
), delim, '');


select strSplit("aaa,b,cc,d", ',', 1) as second;
> b

edited Jun 03 '15 at 12:04

answered Jun 03 '15 at 12:00

vp_arth

14,461
4
37
66

where should i define this function in my store procedure so that i can use it – Muhammad Usama Mashkoor Jun 03 '15 at 12:04
and you always can to use return statement of this function directly. – vp_arth Jun 03 '15 at 12:06

score 0 · Answer 4 · answered Jun 03 '15 at 11:39

0

You can also select the row and store it as a string, and use the explode function to separate the values if you are intending to use the function itself.

answered Jun 03 '15 at 11:39

Everett

35
4

i want this to work in mysql store procedure not in php – Muhammad Usama Mashkoor Jun 03 '15 at 11:41

score 0 · Answer 5 · answered Jul 21 '21 at 04:57

Assuming that exploding will be to create N number of rows, then you can do it like this.

SET @completeString = "Hola,New york,America,!,";
SET @divider = ",";

WITH RECURSIVE strings(m) AS (
    SELECT
        @completeString
    UNION ALL
    SELECT
        SUBSTRING(m, INSTR(m, @divider)+ 1)
    FROM
        strings
    WHERE
        INSTR(m, @divider)!= 0
)
SELECT
    SUBSTRING_INDEX(m, @divider, 1) exploted_strings
FROM
    strings;

That'll give you five rows, including the empty string

+----------------+
|exploted_strings|
+----------------+
| Hola           |
| New york       |
| America        |
| !              |
|                |
+----------------+

Or have it in a Procedure.

CREATE PROCEDURE explode(IN completeString TEXT, IN divider TEXT)
BEGIN
    WITH RECURSIVE strings(m) AS (
        SELECT 
            completeString
        UNION ALL
        SELECT 
            SUBSTRING(m, INSTR(m, divider)+ 1)
        FROM 
            strings 
        WHERE 
            INSTR(m, divider)!= 0
)
SELECT
    SUBSTRING_INDEX(m, divider, 1)
    FROM strings;
END

And call it like

CALL explode ("Hola,New york,America,!,",",");

php explode like function in mysql

5 Answers5