回圈查詢直到找到結果-有解無憂

我無法創建一個簡單的“while 回圈”。

如何回圈選擇查詢并洗掉字串的最后一個字符，直到找到結果？

例如。：

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

將需要導致

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

將需要導致

2stick
2sticks
2stone
2stove

我一直在嘗試這個，但到目前為止沒有運氣

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

uj5u.com熱心網友回復：

正如我所說，你需要一個程式

這使用動態 sql，因為其他代碼容易受到 sql 注入的影響

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')

| 麥田 |
| :-------- |
| 1石 |
| 1 爐 |
| 1石頭 |

?

CALL procedure_name('abc')

| 結果 |
| :--------- |
| 沒有條目 |

?

db<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/468586.html

標籤：mysql sql 细绳循环匹配

上一篇：如何找出給定數字是否在數字串列中？

下一篇：在回圈中更改變數的名稱