Loop a query until result is found-CodePudding

i'm having trouble creating a simple 'while loop'.

How can i loop a select query and remove the last character of a string until results are found?

eg.:

myfield
_______
1stone
1stick
1stove
1stones
2stick
2stove
2stone
2sticks
3stoves
3stone
3stick
3stove


[loop1] SELECT myfield FROM table WHERE myfield LIKE  '1story%'  -> 0 results
[loop2] SELECT myfield FROM table WHERE myfield LIKE  '1stor%'   -> 0 results
[loop3] SELECT myfield FROM table WHERE myfield LIKE  '1sto%'    -> 3 results

would need to result in

1stone
1stones
1stove

[loop1] SELECT myfield FROM table WHERE myfield LIKE '2somevalue%'  -> 0 results 
[loop2] SELECT myfield FROM table WHERE myfield LIKE '2somevalu%'   -> 0 results 
[loop3] SELECT myfield FROM table WHERE myfield LIKE '2someval%'   -> 0 results 
[loop4] SELECT myfield FROM table WHERE myfield LIKE '2someva%'   -> 0 results 
[loop5] SELECT myfield FROM table WHERE myfield LIKE '2somev%'   -> 0 results 
[loop6] SELECT myfield FROM table WHERE myfield LIKE '2some%'   -> 0 results 
[loop7] SELECT myfield FROM table WHERE myfield LIKE '2som%'   -> 0 results 
[loop8] SELECT myfield FROM table WHERE myfield LIKE '2so%'  -> 0 results 
[loop9] SELECT myfield FROM table WHERE myfield LIKE '2s%'   -> 4 results

would need to result in

2stick
2sticks
2stone
2stove

i've been trying this but with no luck so far

SET @counter = 0,  @results = 0, @value = 'somevalue'

WHILE ( @counter <= LENGTH(@value) AND @results = 0)
BEGIN
    SET @qry = "SELECT COUNT(myfield) FROM table WHERE myfield LIKE '"   LEFT(@value, LENGTH(@value)-@Counter)   "%'  " 
    SET @results = (SELECT COUNT(myfield) FROM table WHERE myfield LIKE LEFT(@value, LENGTH(@value)-@Counter)   '%') 
    SET @counter  = @Counter    1 
END 


exec @qry

CodePudding user response：

You need a procrdure for this as i said

This uses dynamic sql because the code else would be vulnerable to sql injection

CREATE TABLE tab
    (`myfield` varchar(7))
;
    
INSERT INTO tab
    (`myfield`)
VALUES
    ('1stone'),
    ('1stick'),
    ('1stove'),
    ('1stones'),
    ('2stick'),
    ('2stove'),
    ('2stone'),
    ('2sticks'),
    ('3stoves'),
    ('3stone'),
    ('3stick'),
    ('3stove')
;

CREATE PROCEDURE procedure_name(_searchtext varchar(100))
BEGIN
   DECLARE _mylen int DEFAULT 0;
   SET _mylen := LENGTH(_searchtext);
   myloop: WHILE _myLen > 0 DO
       
       SET @sql = 'SELECT COUNT(*) INTO @i FROM tab WHERE myfield LIKE  ?';
        set @text := (SELECT CONCAT(LEFT(_searchtext,_mylen), '%'));
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @text;
        DEALLOCATE PREPARE stmt;
       IF @i > 0 then
           SET @sql = 'SELECT myfield FROM tab WHERE myfield LIKE  ?';
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @text;
        DEALLOCATE PREPARE stmt;           
           LEAVE myloop;
       end if;
       SET _myLen := _myLen -1;
   END WHILE;
   IF  @i = 0 then
       SELECT 'No entries' AS result;
   END IF;
END;

CALL procedure_name('1story')

| myfield |
| :------ |
| 1stone  |
| 1stove  |
| 1stones |

✓

CALL procedure_name('abc')

| result     |
| :--------- |
| No entries |

✓

db<>fiddle here