Postgresql SELECT if string contains

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