MySQL full text search equivalents to Google search: Difference between revisions

Latest revision as of 14:28, 20 December 2024

AND[edit]

Google search: keyword1 keyword2 same as keyword1 AND keyword2 or keyword1 +keyword2. (1) The following is exact words search. (2) Replace column_name with your column name

Google: 易筋經 AND 吸星大法
MySQL: column_name REGEXP '易筋經' AND column_name REGEXP '吸星大法'
MySQL: column_name LIKE '%易筋經%' AND column_name LIKE '%吸星大法%' (online demo [1])
MySQL: IF(LOCATE('易筋經', column_name) > 0) AND IF(LOCATE('吸星大法, column_name) > 0)
MySQL: column_name LIKE '%易筋經%吸星大法%' AND column_name LIKE '%吸星大法%易筋經%' Trivial for multiple keywords

OR[edit]

Google search: keyword1 OR keyword2

Google: 易筋經 OR 吸星大法
MySQL: column_name REGEXP '易筋經' OR column_name REGEXP '吸星大法'
MySQL: IF(LOCATE('易筋經', column_name) > 0) OR IF(LOCATE('吸星大法, column_name) > 0)
MySQL: column_name LIKE '%易筋經%' OR column_name LIKE '%吸星大法%' (online demo [2])

NOT[edit]

Google search: keyword1 NOT keyword2 same as keyword1 -keyword2

Google: 易筋經 NOT 吸星大法
MySQL: column_name REGEXP '易筋經' AND column_name NOT REGEXP '吸星大法' (online demo)
MySQL: IF(LOCATE('易筋經', column_name) > 0) AND IF(LOCATE('吸星大法, column_name) = 0)
MySQL: column_name LIKE '%易筋經%' AND column_name NOT LIKE '%吸星大法%'

* wildcard operator[edit]

Google * wildcard operator. "Use *, an asterisk character, known as a wildcard, to match one or more words in a phrase" ^[1] (online demo)

Google: 狐狸*叫
MySQL: column_name LIKE '狐狸%叫'^[2]

English issue[edit]

When the keyword is short and written in English e.g. AI, the query result using column_name LIKE '%AI%' may NOT what you want e.g. Tainan, main, hair and so on.

(1) Remove all non-alpha-numeric-characters^[3] (2) REGEXP word boundaries^[4] e.g. (REPLACE(CONVERT(column_name USING ascii), '?', ' ') REGEXP '([[:<:]])AI([[:>:]])')

Cited from MySQL :: MySQL 5.7 Reference Manual :: 12.5.2 Regular Expressions

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

教學文章：解決簡短英文單字的 MySQL 查詢：搜尋 app 而不是 apple

Ignore special characters[edit]

Ignore return symbol and span tag

Example:
- Searched the keywords e.g. "意法" site:ptt.cc on Google and found the search result contains 意 & 法 located in the nearest but different rows. 意 is at the end of the n-th row. 法 is at the beginning of n+1-th row [3].
Approach: (1) remove the html tag (2) remove the return symbol (Carriage return).

Ignore white spaces, Halfwidth and fullwidth symbol (半形字元和全形字元)

Examples:
- Searched the keywords e.g. "嗎有" on Google and found the search result contains 嗎？有 & 嗎- 有.
- Searched the keywords e.g. "人物誌Persona" on Google and found the search result contains 人物誌（Persona）, 人物誌(Persona) & 「人物誌」（persona）.
Approach: (1) remove the space symbol (2) remove the Halfwidth and fullwidth symbol.
References: PHP remove symbols from string - Stack Overflow

Highlight search query keywords on resulting pages[edit]

Returned result: Show 10 characters before or after the search keywords. (cf: Total 130 ~ 240 characters on Google resulting pages.)

MySQL approach[edit]

SQL syntax[edit]

Input search keywords, and returned the the first occurrence of matched paragraph. Using MySQL SUBSTRING() function, POSITION() function & CHAR_LENGTH() function.


SET @term := "吸星大法";
SET @message := "笑傲江湖中嵩山派掌門左冷禪所創掌法，可發出至陰至寒的真氣。左冷禪與任我行比武時，以此功對付吸星大法，使其全身凍僵、天池穴被封;與岳不群比劍奪帥時，左又使出寒冰神掌，與紫霞神功旗鼓相當、不分勝敗。

原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html";


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`

-- Returned result of scrapbook column: Show 10 characters before or after the search keywords.
-- 行比武時，以此功對付吸星大法，使其全身凍僵、天池

Run on sqlfiddle

Instruction of SQL syntax[edit]

(1) MySQL POSITION() function - w3resource "MySQL POSITION() returns the position of a substring within a string."

SET @term := "吸星大法";
SET @message := "笑傲江湖中嵩山派掌門左冷禪所創掌法，可發出至陰至寒的真氣。左冷禪與任我行比武時，以此功對付吸星大法，使其全身凍僵、天池穴被封;與岳不群比劍奪帥時，左又使出寒冰神掌，與紫霞神功旗鼓相當、不分勝敗。

原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html";
SELECT POSITION(@term IN @message)

-- > returns 46

(2) Avoid the the start position is 0 or negative. Minimum start position of each paragraph is 1.

SELECT IF(
            POSITION(@term IN @message) > 0 &&
            POSITION(@term IN @message) -10 < 0
            , 1
            , POSITION(@term IN @message) -10)

-- > returns 36 = 46 - 10

(3) Show 10 characters before or after the search keywords. MySQL SUBSTRING() function - w3resource"returns a specified number of characters from a particular position of a given string."


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 行比武時，以此功對付吸星大法，使其全身凍僵、天池

SET @term := "吸星大法";
SET @message := "原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html";


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`

-- Returned result of scrapbook column: Show 10 characters before or after the search keywords.
-- [EMPTY]

Google sheet approach[edit]

Using REGEXEXTRACT function case-sensitive!:

	A	B
1	文章	笑傲江湖中嵩山派掌門左冷禪所創掌法，可發出至陰至寒的真氣。左冷禪與任我行比武時，以此功對付吸星大法，使其全身凍僵、天池穴被封;與岳不群比劍奪帥時，左又使出寒冰神掌，與紫霞神功旗鼓相當、不分勝敗。原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html
2	關鍵字	吸星大法
3	搜尋結果摘要	=IF(ISERROR(REGEXEXTRACT(LOWER(B1), "(.{10}"&B2&".{10})")), "", REGEXEXTRACT(LOWER(B1), "(.{10}"&B2&".{10})"))

1. English Keyword Version - "AI agent/agents" Create a Google Sheets formula that suggests a title by extracting text leading up to the "AI agent" mention. case-insensitive!:

=IF(
  REGEXMATCH(A2, "(?i)\bAI\s*agents?\b"),
  REGEXEXTRACT(
    A2,
    ".{0,10}(?i)\bAI\s*agents?\b.{0,10}"
  )&" ...",
  ""
)

Here's a breakdown of the Google Sheets formula that extracts excerpts containing "AI agent" or "AI agents":

The formula has two main parts:

REGEXMATCH to check if the phrase exists
REGEXEXTRACT to get the surrounding context if found

Pattern explanation:

`(?i)` makes the match case-insensitive
`\b` ensures word boundaries
`\s*` allows any number of spaces
`s?` makes the 's' optional (matches both singular and plural)

The formula will:

Search for "AI agent" or "AI agents" in cell A2
If found, extract up to 10 characters before and after the match
Add "..." to indicate truncation
Return empty string if no match

Will match:

"AI agent"
"AI agents"
"ai Agent"
"Ai AGENTS"
"The AI agent is"
"Multiple AI agents are"

Won't match:

"AIagent"
"AImagent"
"AI agentify"

2. Chinese Keyword Version - "AI代理" or "AI 代理" Create a Google Sheets formula that suggests a title by extracting text containing "AI代理". case-insensitive!:

=IF(
  REGEXMATCH(A2, "(?i)\bAI\s*代理"),
  REGEXEXTRACT(
    A2,
    ".{0,10}(?i)\bAI\s*代理.{0,10}"
  )&" ...",
  ""
)

Here's a breakdown of the Google Sheets formula that extracts excerpts containing "AI代理":

The formula has two main parts:

REGEXMATCH to check if the phrase exists
REGEXEXTRACT to get the surrounding context if found

Pattern explanation:

`(?i)` makes the match case-insensitive (affects the "AI" part)
`\b` ensures word boundary before "AI"
`\s*` allows any number of spaces between "AI" and "代理"

The formula will:

Search for "AI代理" or "AI 代理" in cell A2
If found, extract up to 10 characters before and after the match
Add "..." to indicate truncation
Return empty string if no match

Will match:

"AI代理"
"AI 代理"
"ai代理"
"ai 代理"
"This is AI代理 system"
"About AI 代理 research"

Won't match:

"AI代理人" (AI agent person)
"智能代理" (Intelligent agent)
"代理AI" (Agent AI)

Microsoft Spreadsheet approach[edit]

Using FIND, MID & CONCATENATE functions. FIND function is case-sensitive!

	A	B
1	文章	笑傲江湖中嵩山派掌門左冷禪所創掌法，可發出至陰至寒的真氣。左冷禪與任我行比武時，以此功對付吸星大法，使其全身凍僵、天池穴被封;與岳不群比劍奪帥時，左又使出寒冰神掌，與紫霞神功旗鼓相當、不分勝敗。原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html
2	關鍵字	吸星大法
3	搜尋結果摘要	=IF(ISERROR(FIND(B2, B1)), "", CONCATENATE(MID(B1, IF(FIND(B2, B1)-10 >= 1, FIND(B2, B1)-10, 1), 10), MID(B1, FIND(B2, B1), 10+LEN(B2))))

Try it online

PHP approach[edit]

PHP solution: php - highlight multiple keywords in search - Stack Overflow Unverified

Ranking factors[edit]

Possibile factors

Google PageRank - Wikipedia
MySQL POSITION() function - w3resource / MySQL LENGTH() function - w3resource where the keywords located.

References[edit]

↑ Google's * Wildcard Operator - Google Guide
↑ Using MySQL LIKE Operator to Select Data Based on Pattern Matching
↑ mysql - How to remove all non-alpha numeric characters from a string? - Stack Overflow
↑ regex - MySQL REGEXP word boundaries [[:<:]] [[:>:]] and double quotes - Stack Overflow https://stackoverflow.com/questions/18901704/mysql-regexp-word-boundaries-and-double-quotes

[1] Google's * Wildcard Operator - Google Guide

[2] Using MySQL LIKE Operator to Select Data Based on Pattern Matching

[3] ysql - How to remove all non-alpha numeric characters from a string? - Stack Overflow

[4] regex - MySQL REGEXP word boundaries [[:<:]] [[:>:]] and double quotes - Stack Overflow https://stackoverflow.com/questions/18901704/mysql-regexp-word-boundaries-and-double-quotes

[1]

[2]

[3]

[4]

MySQL full text search equivalents to Google search: Difference between revisions

Latest revision as of 14:28, 20 December 2024

Contents

AND[edit]

OR[edit]

NOT[edit]

* wildcard operator[edit]

English issue[edit]

Ignore special characters[edit]

Highlight search query keywords on resulting pages[edit]

MySQL approach[edit]

SQL syntax[edit]

Instruction of SQL syntax[edit]

Google sheet approach[edit]

Microsoft Spreadsheet approach[edit]

PHP approach[edit]

Ranking factors[edit]

Related articles[edit]

References[edit]

Navigation menu

MySQL full text search equivalents to Google search: Difference between revisions

Latest revision as of 14:28, 20 December 2024

AND[edit]

OR[edit]

NOT[edit]

* wildcard operator[edit]

English issue[edit]

Ignore special characters[edit]

Highlight search query keywords on resulting pages[edit]

MySQL approach[edit]

SQL syntax[edit]

Instruction of SQL syntax[edit]

Google sheet approach[edit]

Microsoft Spreadsheet approach[edit]

PHP approach[edit]

Ranking factors[edit]

Related articles[edit]

References[edit]

Navigation menu

Search