276

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??

Improve this question

6 Answers 6

Reset to default
309

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 || '%';

And remember that LIKE is case-sensitive. If you need a case-insensitive comparison, you could do this:

SELECT id FROM TAG_TABLE WHERE 'aaaaaaaa' LIKE '%' || LOWER(tag_name) || '%';
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

what hapoens when tag_name is "; drop table TAG_TABLE; --"?
@Denis: Nothing happens. You get no row, because the WHERE clause evaluates to FALSE. The statement is not dynamic, only values are concatenated, no chance for SQL injection.
shouldn't be the order of aaaa and tag_name reversed? i mean that you should put a column name after where
Beware that using variables in a LIKE pattern may have unintended consequences when those variables contain underscores (_) or percent characters (%). It may be necessary to escape these characters, for example with this function: CREATE OR REPLACE FUNCTION quote_for_like(text) RETURNS text LANGUAGE SQL IMMUTABLE AS $$ SELECT regexp_replace($1, '([\%_])', '\\\1', 'g'); $$; (from user MatheusOl from the #postgresql IRC channel on Freenode).
This didn't work but this did.
|
134

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;
Improve this answer

5 Comments

This is the right way to do this. No one should use the hacky regex approaches.
LIKE and ILIKE can use gin indices. position cannot.
@Grigory There must’ve been a typo in your position.
is it case-sensitive or case-insensitive by default?
One thing non-intuitive thing is that for postgres, position is 1 based, not 0 based. For example select position('a' in 'abc') returns 1. And if there is no match it returns 0.
90

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. `

Improve this answer

3 Comments

This works only when tag_name is a proper REGEX. Pretty risky.
@JakubFedyczak to match literal tag_name you can use ***= which is mentioned in postgresql.org/docs/current/static/functions-matching.html. However I have found that to be too much slower compared to the strpos/position solutions.
Regexes need to be compiled before they can be matched, so matching against a column name like this means it has to compile a new regex for each row considered, which will be very slow.
30

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.

Improve this answer

1 Comment

I had to replace strpos with position, as strpos always returned 0 for me
2

Try This Simple one

Column Name like '%Vesta%'

Improve this answer

Comments

-6 
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

Improve this answer

3 Comments

This is exactly the opposite of the accepted answer. You are concatenating as string while it needs to be a column...
double quotes still reference a column, so this would just be identical
⸻in cases where your column name has spaces.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.