[sql] 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??

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