15,032
edits
MySQL full text search equivalents to Google search: Difference between revisions
Jump to navigation
Jump to search
MySQL full text search equivalents to Google search (edit)
Revision as of 18:29, 20 April 2018
, 20 April 2018→MySQL approach: + instruction
(→References or related articles: update RSS feed URL) |
(→MySQL approach: + instruction) |
||
| Line 85: | Line 85: | ||
</pre> | </pre> | ||
Instructions: | |||
(1) [https://www.w3resource.com/mysql/string-functions/mysql-position-function.php MySQL POSITION() function - w3resource] "MySQL POSITION() returns the position of a substring within a string." | |||
<pre> | |||
SET @term := "吸星大法"; | |||
SET @message := "笑傲江湖中嵩山派掌門左冷禪所創掌法，可發出至陰至寒的真氣。左冷禪與任我行比武時，以此功對付吸星大法，使其全身凍僵、天池穴被封;與岳不群比劍奪帥時，左又使出寒冰神掌，與紫霞神功旗鼓相當、不分勝敗。 | |||
原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html"; | |||
SELECT POSITION(@term IN @message) | |||
-- > returns 46 | |||
</pre> | |||
(2) Avoid the the start position is 0 or negative. Minimum start position of each paragraph is 1. | |||
<pre> | |||
SELECT IF( | |||
POSITION(@term IN @message) > 0 && | |||
POSITION(@term IN @message) -10 < 0 | |||
, 1 | |||
, POSITION(@term IN @message) -10) | |||
-- > returns 36 = 46 - 10 | |||
</pre> | |||
(3) Show 10 characters before or after the search keywords. [https://www.w3resource.com/mysql/string-functions/mysql-substring-function.php MySQL SUBSTRING() function - w3resource]"returns a specified number of characters from a particular position of a given string." | |||
<pre> | |||
SELECT | |||
@message | |||
, CASE | |||
WHEN POSITION(@term IN @message) > 0 THEN SUBSTRING(@message | |||
, IF( | |||
POSITION(@term IN @message) > 0 && | |||
POSITION(@term IN @message) -10 < 0 | |||
, 1 | |||
, POSITION(@term IN @message) -10) | |||
, CHAR_LENGTH(@term) + 20 | |||
) | |||
ELSE '' | |||
END AS "scrapbook"; | |||
-- > returns 行比武時，以此功對付吸星大法，使其全身凍僵、天池 | |||
</pre> | |||
<pre> | <pre> | ||