SQL Postgresql SELECT 如果字符串包含
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23320945/
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
Postgresql SELECT if string contains
提问by user2436815
So I have a in my Postgresql:
所以我在我的 Postgresql 中有一个：
TAG_TABLE
==========================
id tag_name
--------------------------
1 aaa
2 bbb
3 ccc
To simplify my problem, What I want to do is SELECT 'id' from TAG_TABLE when a string "aaaaaaaa" contains the 'tag_name'. So ideally, it should only return "1", which is the ID for tag name 'aaa'
为了简化我的问题，当字符串“aaaaaaaa”包含“tag_name”时，我想要做的是从 TAG_TABLE SELECT 'id'。所以理想情况下，它应该只返回“1”，这是标签名称“aaa”的 ID
This is what I am doing so far:
这是我目前正在做的事情：
SELECT id FROM TAG_TABLE WHERE 'aaaaaaaaaaa' LIKE '%tag_name%'
But obviously, this does not work, since the postgres thinks that '%tag_name%' means a pattern containing the substring 'tag_name' instead of the actual data value under that column.
但显然，这不起作用，因为 postgres 认为 '%tag_name%' 表示包含子字符串 'tag_name' 而不是该列下的实际数据值的模式。
How do I pass the tag_name to the pattern??
如何将 tag_name 传递给模式？
回答by Frans van Buul
You should use 'tag_name' outside of quotes; then its interpreted as a field of the record. Concatenate using '||' with the literal percent signs:
您应该在引号之外使用 'tag_name'；然后将其解释为记录的字段。使用“||”连接 带有字面百分号：
SELECT id FROM TAG_TABLE WHERE 'aaaaaaaa' LIKE '%' || tag_name || '%';
回答by keithhackbarth
I personally prefer the simpler syntax of the ~ operator.
我个人更喜欢 ~ 运算符的简单语法。
SELECT id FROM TAG_TABLE WHERE 'aaaaaaaa' ~ tag_name;
Worth reading through Difference between LIKE and ~ in Postgresto understand the difference. `
值得阅读Postgres 中 LIKE 和 ~ 之间的差异以了解差异。`
回答by Tometzky
A proper way to search for a substring is to use positionfunction instead of likeexpression, which requires escaping %, _and an escape character (\by default):
以搜索一个子一个适当的方法是使用position函数代替like表达，这需要逸出%，_和转义字符（\默认情况下）：
SELECT id FROM TAG_TABLE WHERE position(tag_name in 'aaaaaaaaaaa')>0;
回答by Joop Eggen
In addition to the solution with 'aaaaaaaa' LIKE '%' || tag_name || '%'there
are position(reversed order of args) and strpos.
除了'aaaaaaaa' LIKE '%' || tag_name || '%'有position（args 的倒序）和strpos.
SELECT id FROM TAG_TABLE WHERE strpos('aaaaaaaa', tag_name) > 0
Besides what is more efficient (LIKE looks less efficient, but an index might change things), there is a very minor issue with LIKE: tag_name of course should not contain %and especially _(single char wildcard), to give no false positives.
除了更有效的东西（LIKE 看起来效率较低，但索引可能会改变一些东西），LIKE 有一个非常小的问题：tag_name 当然不应该包含%，尤其是_（单字符通配符），以免产生误报。
回答by Shweta Verma
SELECT id FROM TAG_TABLE WHERE 'aaaaaaaa' LIKE '%' || "tag_name" || '%';
tag_nameshould be in quotation otherwise it will give error as tag_name doest not exist
tag_name应该在引号中，否则会报错，因为 tag_name 不存在
