需要幫助從具有以下內(nèi)容的表一列構(gòu)成MYSQL查詢Row1 : this is first <a href='mytext.txt'>row</a> from the tableRow 2 : THis is the second row <img src ='mytext.jpg'> my image is thereRow 3 : <p>This is the Third row my mytext is there </p>Row 4 : <p class='te_mytext'>This is the Third row my text is there </p>這是表格行，我嘗試將關(guān)鍵字搜索為“ mytext”我的查詢是SELECT * from table WHERE colmn_name ` like '%mytext%' "我將得到所有4行作為結(jié)果，但結(jié)果是錯(cuò)誤的。我只需要第3行就可以得到正確的輸出。之所以只在內(nèi)容中包含mytext的該行不在內(nèi)容中，而是在所有行中都具有mytext的原因，如何編寫MySQL查詢？
3 回答
狐的傳說(shuō)
TA貢獻(xiàn)1804條經(jīng)驗(yàn) 獲得超3個(gè)贊
這是我對(duì)strip_tags函數(shù)的實(shí)現(xiàn)：
CREATE FUNCTION `strip_tags`($str text) RETURNS text
BEGIN
DECLARE $start, $end INT DEFAULT 1;
LOOP
SET $start = LOCATE("<", $str, $start);
IF (!$start) THEN RETURN $str; END IF;
SET $end = LOCATE(">", $str, $start);
IF (!$end) THEN SET $end = $start; END IF;
SET $str = INSERT($str, $start, $end - $start + 1, "");
END LOOP;
END;
我確保它刪除了不匹配的左括號(hào)，因?yàn)樗鼈兒芪ｋU(xiǎn)，盡管它忽略了任何未配對(duì)的右括號(hào)，因?yàn)樗鼈儫o(wú)害。
mysql> select strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.');
+----------------------------------------------------------------------+
| strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.') |
+----------------------------------------------------------------------+
| hello world again. |
+----------------------------------------------------------------------+
1 row in set
Smart貓小萌
TA貢獻(xiàn)1911條經(jīng)驗(yàn) 獲得超7個(gè)贊
這些行添加到fnStripTags功能
后SET Dirty = Insert( Dirty, iStart, iLength, '');
set Dirty = Replace(Dirty,' ',''); #No space between & and nbsp;
set Dirty = Replace(Dirty,'\r','');
set Dirty = Replace(Dirty,'\n','');
添加回答
舉報(bào)
0/150
提交
取消