postgresql Postgres 正则表达式子字符串或 regexp_matches 的实际示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3403754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pratical example of Postgres regex substring or regexp_matches
提问by Joey
I have been trying to figure the following for a couple days. Please HELP
几天来，我一直在尝试解决以下问题。请帮忙
PostgreSQL table : locations
PostgreSQL 表：位置
Id State
--------------------
1 New York
2 Texas
input = 'Greetings from Texas to all Cowboys'
input = '来自德克萨斯州的问候所有牛仔'
output: row containing Texas
输出：包含德克萨斯的行
SELECT id, state FROM locations WHERE state ~* substring(input from state)
SELECT id, state FROM locations WHERE state ~* substring(input from state)
回答by tinychen
1.
1.
select * from locations where 'Greetings from Texas to all Cowboys' ~ State;
2.
2.
select * from locations where State = any(string_to_array('Greetings from Texas to all Cowboys',' '));
The two methods above both have some problems in some circumstances.But I want to know if they are for you.
以上两种方法在某些情况下都有一些问题。但我想知道它们是否适合您。
3.
3.
select * from locations where 'reetings from Texas to all Cowboys' ~* ('\m' || state || '\M');
The last method would be more better.
最后一种方法会更好。
回答by Nordic Mainframe
A search word is not a pattern. Try this:
搜索词不是模式。尝试这个：
select * from locations where 'Hello from Texas!' like '%' || state || '%';
or this:
或这个：
select * from locations where 'Hello from Texas!' ~* ('.*' || state || '.*');
if you want Posix regexp's.
如果你想要 Posix 正则表达式。
Example:
例子：
# create table locations(id integer, state text);
CREATE TABLE
# insert into locations values (1,'New York'),(2,'Texas') ;
INSERT 0 2
# select * from locations where 'Hello from Texas!' like '%' || state || '%';
id | state
----+-------
2 | Texas
(1 row)
# select * from locations where 'Hello from Texas!' ~* ('.*' || state || '.*');
id | state
----+-------
2 | Texas
(1 row)
# select * from locations where 'Greetings from you ex' like '%' || state || '%';
id | state
----+-------
(0 rows)
# select * from locations where 'Greetings from your ex' ~* ('.*' || state || '.*');
id | state
----+-------
(0 rows)
This needs some refinement or course, if you need to detect word boundaries:
如果您需要检测单词边界，这需要一些改进或课程：
# select * from locations where 'fakulos greekos metexas' ~* ('.*' || state || '.*');
id | state
----+-------
2 | Texas
If you have regex-metacharacters (See the PostgresSQL docs for as list) in your search words, then you might need to quote them first. This look a bit weird but this is what escaping always looks like:
如果您的搜索词中有正则表达式元字符（请参阅 PostgresSQL 文档作为列表），那么您可能需要先引用它们。这看起来有点奇怪，但这就是逃避的样子：
select regexp_replace('Dont mess (with) Texas, The Lone *',E'([\(\)\*])',E'\\\1','g');
The ([\(\)\*])is the list of characters you want to escape.
这([\(\)\*])是要转义的字符列表。
However, if you neverneed regular expressions in your search words, then it might be easier to use a simple string searching function like strpos():
但是，如果您从不需要在搜索词中使用正则表达式，那么使用像 strpos() 这样的简单字符串搜索函数可能会更容易：
select strpos('Dont mess (with) Texas','Texas')>0;
?column?
--------
t
select strpos('Dont mess (with) Texas','Mars')>0;
?column?
--------
f
You can use upper()if you want case insensitive compares
upper()如果您想要不区分大小写的比较，您可以使用
select strpos(upper('Dont mess (with) Texas'),upper('teXas'))>0;
?column?
--------
t
回答by Frank Heikens
I would take a look at full text search:
我会看一下全文搜索：
SELECT
id,
state
FROM
locations
WHERE
to_tsvector('english', 'Greetings from Texas to all Cowboys') @@ plainto_tsquery('english', state);
Standard available as of version 8.3, in older versions you have to install tsearch2 from the contrib.
从 8.3 版开始提供标准，在旧版本中，您必须从 contrib 安装 tsearch2。
http://www.postgresql.org/docs/current/interactive/textsearch.html
http://www.postgresql.org/docs/current/interactive/textsearch.html
