MySQL split string and then select

Question

I've searched the interwebs and SO for a solution to what I'm trying to do but can't find said solution so...

I have a stored procedure that accepts one parameter, my_str. If my_str has spaces within the string (let's say "now is the winter of our discontent"), I want to iterate through that string and generate a WHERE clause. IE if my_str = "now is the winter of our discontent", I want to generate something like the following:

SELECT id, title, author
FROM my_table
where (my_content like '%now%'
    or my_content like '%is%'
    OR my_content like '%the%'
    OR my_content like '%winter%'
    OR my_content like '%of%'
    OR my_content like '%our%'
    OR my_content like '%discontent%'
);

Any guidance is greatly appreciated.

you want to do the splitting/sql-building inside your stored proc? or in some client app? — Marc B, Mar 11 '15 at 19:27

score 0 · Accepted Answer · edited May 23 '17 at 12:28

0

Create sql string and use PREPARE

    SET @s = CONCAT("SELECT id, title, author FROM my_table where (my_content like '%", REPLACE($myStr, " ", "%' OR my_content LIKE '%"), "%');");
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

Read this for more info: How To have Dynamic SQL in MySQL Stored Procedure

edited May 23 '17 at 12:28

Community

1
1

answered Mar 11 '15 at 19:30

cyadvert

855
7
19

MySQL split string and then select

1 Answers1