PHP Explode method in sql query

Question

i have a problem here

is there a way to explode things up in sql query and make a group by after it ? actually i want use exactly php explode-like function in a sql query, is there a way to do that ?

test{explode}test2{explode}test2{explode}test3{explode}test{explode}test

return :

test  `count 3`

test2 `count 2`

test3 `count 1`

here's a sqlfiddle : http://sqlfiddle.com/#!2/b318d/1

Before when @Anthony? i want something like mysqli_query($sqli,'SELECT * FROM test WHERE explode("{explode}",value) GROUP BY explodedResult'); — Saeid Khaleghi
– Saeid Khaleghi, Commented Apr 22, 2014 at 7:32
So if I'm reading right, you are storing values as a comma delimited string? That's bad. — Anthony
– Anthony, Commented Apr 22, 2014 at 8:56
And also what does WHERE explode("{explode}",value) mean? What is it comparing it to? — Anthony
– Anthony, Commented Apr 22, 2014 at 8:58

Jerko W. Tisler · Accepted Answer · 2014-04-22 07:32:47Z

2

you can make your own function and then use it in query

here is an example by Federico Cargnelutti http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

For example

SELECT SPLIT_STR(`field_name`, ',', 0) 
FROM TEST
GROUP BY SPLIT_STR(`field_name`, ',', 0)

answered Apr 22, 2014 at 7:32

Jerko W. Tisler

98910 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Saeid Khaleghi Over a year ago

What is it means : This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled i tried to put the function in PHPMYADMIN Sql query and get that.

Jerko W. Tisler Over a year ago

you can read more about it here dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html. you need to execute this query SET GLOBAL log_bin_trust_function_creators = 1; before creating function to avoid that error

Saeid Khaleghi Over a year ago

i did that, i created the function and lunched the query you gave. still not workin

Jerko W. Tisler Over a year ago

are you getting error or you are not satisfied with the result?

Collectives™ on Stack Overflow

PHP Explode method in sql query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related