Postgresql SELECT if string contains

Question

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

score 196 · Accepted Answer · edited Apr 06 '22 at 12:01

196

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

edited Apr 06 '22 at 12:01

Eduardo Cuomo

16,091
5
104
88

answered Apr 27 '14 at 08:18

Frans van Buul

2,291
1
12
6

8

what hapoens when tag_name is `"; drop table TAG_TABLE; --"`? – Denis de Bernardy Apr 27 '14 at 10:21
31

@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. – Erwin Brandstetter Apr 27 '14 at 10:48
1

shouldn't be the order of aaaa and tag_name reversed? i mean that you should put a column name after where – user151496 May 11 '15 at 15:10
@user151496 No because the pattern has to go on the right side of the `LIKE` keyword. – jpmc26 Jun 21 '16 at 00:10
8

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). – Martin von Wittich Sep 13 '16 at 15:11
This didn't work but [this](https://stackoverflow.com/a/30379009/14967240) did. – Mahmood Nov 13 '21 at 07:29
Use `ILIKE` for case insensitive search. – AJP Apr 08 '22 at 12:24

score 74 · Answer 2 · answered Sep 12 '18 at 15:23

74

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;

answered Sep 12 '18 at 15:23

Tometzky

20,923
4
58
71

2

This is the right way to do this. No one should use the hacky regex approaches. – khol Apr 01 '20 at 19:33
`LIKE` and `ILIKE` can use `gin` indices. `position` cannot. – Eugene Pakhomov Aug 02 '20 at 11:16
1

@Grigory There must’ve been a typo in your `position`. – Константин Ван Mar 24 '21 at 23:31

score 68 · Answer 3 · edited Apr 06 '22 at 12:02

68

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

edited Apr 06 '22 at 12:02

Eduardo Cuomo

16,091
5
104
88

answered Jan 26 '16 at 18:01

keithhackbarth

8,018
4
25
31

4

This works only when `tag_name` is a proper REGEX. Pretty risky. – Jakub Fedyczak Aug 23 '17 at 09:17
1

@JakubFedyczak to match literal tag_name you can use `***=` which is mentioned in https://www.postgresql.org/docs/current/static/functions-matching.html. However I have found that to be too much slower compared to the `strpos`/`position` solutions. – phunehehe Oct 09 '18 at 16:33

score 23 · Answer 4 · answered Sep 12 '18 at 15:49

23

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.

answered Sep 12 '18 at 15:49

Joop Eggen

102,262
7
78
129

2

I had to replace strpos with position, as strpos always returned 0 for me – jcf Jan 21 '20 at 16:32

score -4 · Answer 5 · edited Jul 01 '19 at 08:04

-4

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

edited Jul 01 '19 at 08:04

Suraj Rao

28,850
10
94
99

answered Jul 01 '19 at 08:03

Shweta Verma

1
2

2

This is exactly the opposite of the accepted [answer](https://stackoverflow.com/a/23321017/4826457). You are concatenating as string while it needs to be a column... – Suraj Rao Jul 01 '19 at 08:07
3

double quotes still reference a column, so this would just be identical – vpzomtrrfrt Feb 04 '21 at 19:25
⸻in cases where your column name has spaces. – Константин Ван May 08 '21 at 22:22

Postgresql SELECT if string contains

5 Answers5

Linked