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 15:33, 30 September 2017
, 30 September 2017→Highlight search query keywords on resulting pages
m (→English issue) |
|||
| Line 55: | Line 55: | ||
Total 130 ~ 240 characters on Google resulting pages. | Total 130 ~ 240 characters on Google resulting pages. | ||
=== MySQL approach === | |||
Input search keywords, and returned the matched paragraph. Using MySQL [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php SUBSTRING() function], [http://www.w3resource.com/mysql/string-functions/mysql-position-function.php POSITION() function] & [http://www.w3resource.com/mysql/string-functions/mysql-char_length-function.php CHAR_LENGTH() function]. | Input search keywords, and returned the matched paragraph. Using MySQL [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php SUBSTRING() function], [http://www.w3resource.com/mysql/string-functions/mysql-position-function.php POSITION() function] & [http://www.w3resource.com/mysql/string-functions/mysql-char_length-function.php CHAR_LENGTH() function]. | ||
| Line 109: | Line 110: | ||
</pre> | </pre> | ||
=== Google sheet approach === | |||
Using [https://support.google.com/docs/answer/3098244?hl=zh-Hant REGEXEXTRACT] function: | |||
<table border="1"> | |||
<tr> | |||
<td></td> | |||
<td>A</td> | |||
<td>B</td> | |||
</tr> | |||
<tr> | |||
<td>1</td> | |||
<td>文章</td> | |||
<td>笑傲江湖中嵩山派掌門左冷禪所創掌法，可發出至陰至寒的真氣。左冷禪與任我行比武時，以此功對付吸星大法，使其全身凍僵、天池穴被封;與岳不群比劍奪帥時，左又使出寒冰神掌，與紫霞神功旗鼓相當、不分勝敗。 | |||
原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html</td> | |||
</tr> | |||
<tr> | |||
<td>2</td> | |||
<td>關鍵字</td> | |||
<td>吸星大法</td> | |||
</tr> | |||
<tr> | |||
<td>3</td> | |||
<td>搜尋結果摘要</td> | |||
<td>{{kbd | key=<nowiki>=IF(ISERROR(REGEXEXTRACT(B1, "(.{10}"&B2&".{10})")), "", REGEXEXTRACT(B1, "(.{10}"&B2&".{10})"))</nowiki>}}</td> | |||
</tr> | |||
</table> | |||
=== Microsoft Spreadsheet approach === | |||
Using [https://support.office.com/zh-tw/article/FIND%E3%80%81FINDB-%E5%87%BD%E6%95%B8-c7912941-af2a-4bdf-a553-d0d89b0a0628 FIND], [https://support.office.com/zh-tw/article/MID%E3%80%81MIDB-%E5%87%BD%E6%95%B8-d5f9e25c-d7d6-472e-b568-4ecb12433028 MID] & [https://support.office.com/zh-tw/article/CONCATENATE-%E5%87%BD%E6%95%B8-8f8ae884-2ca8-4f7a-b093-75d702bea31d CONCATENATE] functions | |||
<table border="1"> | |||
<tr> | |||
<td></td> | |||
<td>A</td> | |||
<td>B</td> | |||
</tr> | |||
<tr> | |||
<td>1</td> | |||
<td>文章</td> | |||
<td>笑傲江湖中嵩山派掌門左冷禪所創掌法，可發出至陰至寒的真氣。左冷禪與任我行比武時，以此功對付吸星大法，使其全身凍僵、天池穴被封;與岳不群比劍奪帥時，左又使出寒冰神掌，與紫霞神功旗鼓相當、不分勝敗。 | |||
原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html</td> | |||
</tr> | |||
<tr> | |||
<td>2</td> | |||
<td>關鍵字</td> | |||
<td>吸星大法</td> | |||
</tr> | |||
<tr> | |||
<td>3</td> | |||
<td>搜尋結果摘要</td> | |||
<td>{{kbd | key=<nowiki>=IF(ISERROR(FIND(B2, B1)), "", CONCATENATE(MID(B1, FIND(B2, B1)-10, 10), MID(B1, FIND(B2, B1), 10+LEN(B2))))</nowiki>}}</td> | |||
</tr> | |||
</table> | |||
=== PHP approach === | |||
PHP solution: [https://stackoverflow.com/questions/2757556/highlight-multiple-keywords-in-search php - highlight multiple keywords in search - Stack Overflow] ''Unverified'' | |||
== Ranking factors == | == Ranking factors == | ||