如何在Oracle SQL的多列中搜索多个字符串？

要实现你描述的需求——所有搜索关键词必须在表的任意列中找到匹配（即每个关键词至少出现在某一列，才返回对应记录），完全可以用Oracle SQL实现，下面提供几种可行方案：

静态关键词直接查询 #

如果搜索关键词是固定的（比如mainstreet、John、Doe），可以直接用AND结合OR构造条件，每个关键词对应一组列的模糊匹配：

SELECT *
FROM your_table_name
WHERE 
  -- 确保mainstreet存在于任意列
  (fname LIKE '%mainstreet%' OR lname LIKE '%mainstreet%' OR street LIKE '%mainstreet%' OR city LIKE '%mainstreet%' OR TO_CHAR(id) LIKE '%mainstreet%')
  AND
  -- 确保John存在于任意列
  (fname LIKE '%John%' OR lname LIKE '%John%' OR street LIKE '%John%' OR city LIKE '%John%' OR TO_CHAR(id) LIKE '%John%')
  AND
  -- 确保Doe存在于任意列
  (fname LIKE '%Doe%' OR lname LIKE '%Doe%' OR street LIKE '%Doe%' OR city LIKE '%Doe%' OR TO_CHAR(id) LIKE '%Doe%');

说明 #

• 每个AND子句对应一个搜索关键词，内部用OR遍历所有列检查匹配
• 因为id是数值类型，用TO_CHAR(id)转换为字符串后再做模糊匹配，避免类型转换异常
• 如果需要精确匹配，去掉%通配符即可

动态拆分搜索字符串查询 #

如果搜索词是用户输入的空格分隔字符串（比如mainstreet John Doe），可以先拆分关键词再做关联查询，适合关键词数量不固定的场景：

WITH search_terms AS (
  -- 拆分空格分隔的搜索字符串为独立关键词
  SELECT TRIM(REGEXP_SUBSTR('mainstreet John Doe', '[^ ]+', 1, LEVEL)) AS term
  FROM dual
  CONNECT BY REGEXP_SUBSTR('mainstreet John Doe', '[^ ]+', 1, LEVEL) IS NOT NULL
)
SELECT t.*
FROM your_table_name t
-- 关联关键词，匹配任意列
JOIN search_terms st ON 
  t.fname LIKE '%' || st.term || '%'
  OR t.lname LIKE '%' || st.term || '%'
  OR t.street LIKE '%' || st.term || '%'
  OR t.city LIKE '%' || st.term || '%'
  OR TO_CHAR(t.id) LIKE '%' || st.term || '%'
-- 分组后验证所有关键词都匹配成功
GROUP BY t.id, t.fname, t.lname, t.street, t.city
HAVING COUNT(DISTINCT st.term) = (SELECT COUNT(*) FROM search_terms);

说明 #

• 用REGEXP_SUBSTR和CONNECT BY拆分字符串，自动处理任意数量的空格分隔关键词
• 最后通过HAVING子句确保当前记录匹配了所有关键词，避免漏匹配

性能优化方案（Oracle Text） #

如果表数据量较大，模糊匹配的LIKE查询效率会很低，推荐使用Oracle的全文索引功能（Oracle Text）：

1. 先创建覆盖所有需要搜索列的全文索引：

CREATE INDEX idx_person_fulltext ON your_table_name (fname, lname, street, city, TO_CHAR(id)) INDEXTYPE IS CTXSYS.CONTEXT;

2. 用CONTAINS查询实现高效的多关键词全匹配：

SELECT *
FROM your_table_name
WHERE CONTAINS(
  fname || ' ' || lname || ' ' || street || ' ' || city || ' ' || TO_CHAR(id),
  'mainstreet AND John AND Doe'
) > 0;

说明 #

• 全文索引会对文本内容做分词处理，查询效率远高于LIKE模糊匹配
• CONTAINS函数支持AND逻辑，直接确保所有关键词都存在于拼接后的文本中

内容的提问来源于stack exchange，提问作者Supptech