找到你要的答案

Q:Searching for any word or part of it inside string

Q:搜索字符串内的任何单词或部分

I have been trying to implement search query, part of which is searching for a string. What I want to accomplish is search for any word(s) from an example string of "lorem ipsum dolor es" To claryfiy, I would like to find records which have lorem OR lorem ipsum OR dolor OR *lorem ipsum dolor es* if applicable.

What I tried so far:

SIMILAR TO '%lorem ipsum dolor es%' - this obviously returns 0 since not a whole string is found

LIKE LOWER '%lorem ipsum dolor es%' - same as above

to_tsvector(files.desc) @@ 'lorem ipsum dolor es' - also empty result

Do I have to transform users input into and array of words to be able to accomplish my task?

EDIT:

My original idea was to be able to find occurrence of any part of the string in my table, which was solved by using below query. Yet I came across another obstacle. What if I want to search two tables by using one query and search strictly. If user types "lorem ipsum keyword4" I want it to find any image that has description containing any word of lorem ipsum AND that has keyword4 belonging to it. But I also want it to find images if one types "keyword4" or types "lorem ipsum". Below fiddle shows how it works in case one types "lorem ipsum keyword4". How do I do that?

fiddle here

我一直在努力实现搜索查询,其中一部分是搜索一个字符串。我想实现的目标是任何词搜索(S)从“乱数假文“ES”claryfiy例字符串,我想如果可以找到,也许你或乱数假文或悲哀或忧伤乱数假文ES *记录。

我迄今所尝试的:

类似于“%乱数假文悲哀ES %”--这显然不是整个字符串返回0以来发现的

喜欢下“%乱数假文悲哀ES %”-同上

to_tsvector(文件。DESC)@ @乱数假文悲哀的也是空的结果

我必须转换用户输入和数组的话才能完成我的任务吗?

编辑:

我最初的想法是能够找到发生在我的表的字符串的任何部分,这是通过以下查询解决。然而我遇到另一个障碍。如果我想搜索两个表,使用一个查询和严格搜索。如果用户类型”乱数假文keyword4“我要找到任何图像,包含任何乱数假文字描述,keyword4属于它。但我也希望找到一种“图像如果keyword4”或“乱数假文”类型。下面的小提琴展示了它是如何工作的情况下的一个类型”乱数假文keyword4”。我该怎么做?

小提琴在这里

answer1: 回答1:

I've used a function for search. Where p_name is a text input as 'lorem ipsum dolor es'. It finds patient records with name which includes all the words as a substring.

SELECT * FROM patient    
WHERE patient.name LIKE ALL (SELECT DISTINCT '%' || name || '%' FROM UNNEST(STRING_TO_ARRAY(p_name,' ')) AS name)

我已经使用了搜索功能。在p_name是文本输入为“乱数假文悲哀的。发现记录病人的名字,包括所有的词作为子字符串。

SELECT * FROM patient    
WHERE patient.name LIKE ALL (SELECT DISTINCT '%' || name || '%' FROM UNNEST(STRING_TO_ARRAY(p_name,' ')) AS name)
answer2: 回答2:

The fastest and simplest way to find all strings that contain any of the words - treated as natural language words in the English language (default dictionary) in Postgres text search:

SELECT * FROM tbl
WHERE  to_tsvector(string)
   @@  to_tsquery(replace('lorem ipsum dolor es'::text, ' ', '|'));

SQL Fiddle.

Text search is case insensitive.
This can be supported with indexes very efficiently.

发现含有任何字处理中英文自然语言词汇的所有字符串的最快最简单的方法(默认词典)在Postgres的文本搜索:

SELECT * FROM tbl
WHERE  to_tsvector(string)
   @@  to_tsquery(replace('lorem ipsum dolor es'::text, ' ', '|'));

SQL的小提琴。

Text search is case insensitive.
This can be supported with indexes very efficiently.

postgresql  search  sql-like