How to find strings before string -> true?

Question

I have 'animal_check' column with values [dog -> true, rose -> false, elephant -> true, cow -> true, daffodil -> false]

and I would like to have a result 'animal' based on above which only contains

dog,elephant,cow .

How to achieve it? Any suggestion? Thank you :)

[Should I normalize my DB or not?](https://stackoverflow.com/questions/934577/should-i-normalize-my-db-or-not), I think you should. — Luuk, Aug 24 '21 at 10:31
You shouldn't be storing multiple values in a string. It is bad database design. — Gordon Linoff, Aug 24 '21 at 10:33
*I have 'animal_check' column with values* Precise values are unclear. Provide this as CREATE TABLE + INSERT INTO. — Akina, Aug 24 '21 at 10:56

score 0 · Answer 1 · answered Aug 24 '21 at 13:13

Without explanation (because you should normalize your database!):


WITH RECURSIVE cte(y,x,i) AS (
   SELECT space(50) as y , 'dog -> true, rose -> false, elephant -> true, cow -> true, daffodil -> false' as x, 0 as i
   UNION ALL
   SELECT LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(x,','),',',i+1),',',-1),50),x,i+1 
   FROM cte 
   WHERE i<10 and rtrim(x)<>' '
)
SELECT TRIM(SUBSTRING_INDEX(y,'-',1)) as animal 
FROM cte
WHERE y like '%true%';

DBFIDDLE

How to find strings before string -> true?

1 Answers1