Menu
Use the substring() Function to SELECT if String Contains a Substring Match in PostgreSQL. The substring() returns the strings similar to abc in our case or contains abc . We then match the returned results to the str using the ~~ operator, short for like , and if they match, we select the results from the table.
To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause.
The PostgreSQL substring function is used to extract a string containing a specific number of characters from a particular position of a given string. The main string from where the character to be extracted. Optional. The position of the string from where the extracting will be starting.
The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. The percent sign represents zero, one, or multiple numbers or characters.
You should use 'tag_name' outside of quotes; then its interpreted as a field of the record. Concatenate using '||' with the literal percent signs:
SELECT id FROM TAG_TABLE WHERE 'aaaaaaaa' LIKE '%' || tag_name || '%';
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. `
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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With