So I have a in my 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'
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.
How do I pass the tag_name to the pattern??
5 Answers
You should use tag_name outside of quotes; then it's interpreted as a field of the record. Concatenate using '||' with the literal percent signs:
SELECT id FROM TAG_TABLE WHERE 'aaaaaaaa' LIKE '%' || tag_name || '%';
A proper way to search for a substring is to use position function instead of like expression, which requires escaping %, _ and an escape character (\ by default):
SELECT id FROM TAG_TABLE WHERE position(tag_name in 'aaaaaaaaaaa')>0;
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 Postgres to understand the difference. `
In addition to the solution with 'aaaaaaaa' LIKE '%' || tag_name || '%' there
are position (reversed order of args) and 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.
SELECT id FROM TAG_TABLE WHERE 'aaaaaaaa' LIKE '%' || "tag_name" || '%';
tag_name should be in quotation otherwise it will give error as tag_name doest not exist
