Equivalent of Explode() to Work With Strings in MySQL

Equivalent of explode() to work with strings in MySQL

MYSQL has no explode() like function built in. But you can easily add similar function to your DB and then use it from php queries. That function will look like:

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '');

Usage:

SELECT SPLIT_STRING('apple, pear, melon', ',', 1)

The example above will return apple.
I think that it will be impossible to return array in MySQL so you must specify which occurrence to return explicitly in pos. Let me know if you succeed using it.

php explode like function in mysql

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

How to explode in MySQL and use it in the WHERE clause of the query - MySQL

Use , to separate the string and try this query

select * from promotion_table where FIND_IN_SET("23",bungalow_ids)

http://sqlfiddle.com/#!2/7bbcb/1

Split/explode mysql string in numeric values

You can use the function FIND_IN_SET (it is available in the mysql version you specified). It splits the provided string (in your case stored in the variable) using the comma as separator and returns the index of the index of the first occurrence of the specified value (0 if not found)

/* building the list */
> SELECT @mylist :=GROUP_CONCAT(id SEPARATOR ',') FROM users WHERE id < 10;
+-----------------------------------------+
| @mylist:=group_concat(id separator ',') |
+-----------------------------------------+
| 0,2,3,4,5,6,7,8,9                       |
+-----------------------------------------+

> SELECT id, mail FROM users WHERE FIND_IN_SET(id, @mylist);

The automatic casting is enough to automatically manage the comparison between the original type and the final string most of the cases.

---

UPDATE

Although it answers your question, the proposed solution can get slow when looking for huge amounts of ids. A way better solution would be to drop the use of variables and store the results in a temporary table

CREATE [TEMPORARY] TABLE IF NOT EXISTS tmp_users (
  "id" INT,
  PRIMARY KEY (id)
)

INSERT INTO tmp_users (...);

Explode and implode strings in mysql query

Try this:

select concat(last_name," ",first_name) as FullName
from
(
select SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) AS last_name, 
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) AS first_name
from your_table
) tab

See a sample here

http://sqlfiddle.com/#!2/cd4ee/4

EDIT:

Then a slight modified version will do your work. You can refer the same fiddle for a updated sample as per your need.

select concat(last_name," ",first_name) as FullName
from
(
select right(name,(length(name) - instr(name,' '))) as last_name, 
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) AS first_name
from tab
) tab1

Can you split/explode a field in a MySQL query?

MySQL's only string-splitting function is SUBSTRING_INDEX(str, delim, count). You can use this, to, for example:

• Return the item before the first separator in a string:

  mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', 1);
  +--------------------------------------------+
  | SUBSTRING_INDEX('foo#bar#baz#qux', '#', 1) |
  +--------------------------------------------+
  | foo                                        |
  +--------------------------------------------+
  1 row in set (0.00 sec)
  

• Return the item after the last separator in a string:

  mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', -1);
  +---------------------------------------------+
  | SUBSTRING_INDEX('foo#bar#baz#qux', '#', -1) |
  +---------------------------------------------+
  | qux                                         |
  +---------------------------------------------+
  1 row in set (0.00 sec)
  

• Return everything before the third separator in a string:

  mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', 3);
  +--------------------------------------------+
  | SUBSTRING_INDEX('foo#bar#baz#qux', '#', 3) |
  +--------------------------------------------+
  | foo#bar#baz                                |
  +--------------------------------------------+
  1 row in set (0.00 sec)
  

• Return the second item in a string, by chaining two calls:

  mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('foo#bar#baz#qux', '#', 2), '#', -1);
  +----------------------------------------------------------------------+
  | SUBSTRING_INDEX(SUBSTRING_INDEX('foo#bar#baz#qux', '#', 2), '#', -1) |
  +----------------------------------------------------------------------+
  | bar                                                                  |
  +----------------------------------------------------------------------+
  1 row in set (0.00 sec)
  

In general, a simple way to get the nth element of a #-separated string (assuming that you know it definitely has at least n elements) is to do:

SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, '#', n), '#', -1);

The inner SUBSTRING_INDEX call discards the nth separator and everything after it, and then the outer SUBSTRING_INDEX call discards everything except the final element that remains.

If you want a more robust solution that returns NULL if you ask for an element that doesn't exist (for instance, asking for the 5th element of 'a#b#c#d'), then you can count the delimiters using REPLACE and then conditionally return NULL using IF():

IF(
    LENGTH(your_string) - LENGTH(REPLACE(your_string, '#', '')) / LENGTH('#') < n - 1,
    NULL,
    SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, '#', n), '#', -1)
)

Of course, this is pretty ugly and hard to understand! So you might want to wrap it in a function:

CREATE FUNCTION split(string TEXT, delimiter TEXT, n INT)
RETURNS TEXT DETERMINISTIC
RETURN IF(
    (LENGTH(string) - LENGTH(REPLACE(string, delimiter, ''))) / LENGTH(delimiter) < n - 1,
    NULL,
    SUBSTRING_INDEX(SUBSTRING_INDEX(string, delimiter, n), delimiter, -1)
);

You can then use the function like this:

mysql> SELECT SPLIT('foo,bar,baz,qux', ',', 3);
+----------------------------------+
| SPLIT('foo,bar,baz,qux', ',', 3) |
+----------------------------------+
| baz                              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SPLIT('foo,bar,baz,qux', ',', 5);
+----------------------------------+
| SPLIT('foo,bar,baz,qux', ',', 5) |
+----------------------------------+
| NULL                             |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SPLIT('foo###bar###baz###qux', '###', 2);
+------------------------------------------+
| SPLIT('foo###bar###baz###qux', '###', 2) |
+------------------------------------------+
| bar                                      |
+------------------------------------------+
1 row in set (0.00 sec)

Explode the values in the database

You are doing things in the wrong place, the explode and echo has to be inside the loop or you will only ever do anything with the last occurance in the $result array.

$result = $wpdb->get_results( "SELECT DISTINCT values_id FROM wp_badges");
foreach($result as $value){
    $bits = explode(',',$value->values_id);
    echo '"' . implode('","', $bits) . '"<br>';
}

RESULT

"123","456"<br>
"923","956"<br>

Searching database by string exploded into array

I think you could do something like this

WHERE tags LIKE ’$exploded_search_string[0]%' OR tags LIKE '%$exploded_search_string[1]%'

OR

WHERE tags REGEXP '$exploded_search_string[0]| $exploded_search_string[1]'

You can loop the $exploded_search_string array to create the SQL statement like above and your select query should find all the tags found in DB.

Once you be able to get the results using the above statement, you could check FULL TEXT search for an optimized solution.

---

Related Topics