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 267 · Accepted Answer · edited Dec 11 '22 at 11:46

267

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

edited Dec 11 '22 at 11:46

Dominus.Vobiscum

702
1
9
16

answered Apr 27 '14 at 08:18

Frans van Buul

3,009
1
13
6

12

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

@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
11

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
1

This didn't work but [this](https://stackoverflow.com/a/30379009/14967240) did. – Mahmood Nov 13 '21 at 07:29
1

Use `ILIKE` for case insensitive search. – AJP Apr 08 '22 at 12:24

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

103

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

22,573
5
59
73

4

This is the right way to do this. No one should use the hacky regex approaches. – khol Apr 01 '20 at 19:33
3

`LIKE` and `ILIKE` can use `gin` indices. `position` cannot. – Eugene Pakhomov Aug 02 '20 at 11:16
3

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

is it case-sensitive or case-insensitive by default? – farisfath25 May 02 '23 at 02:07

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

81

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

17,828
6
117
94

answered Jan 26 '16 at 18:01

keithhackbarth

9,317
5
28
33

5

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
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. – harmic Jan 27 '23 at 04:34

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

27

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

107,315
7
83
138

3

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

score 1 · Answer 5 · answered May 11 '23 at 10:53

1

Try This Simple one

Column Name like '%Vesta%'

answered May 11 '23 at 10:53

Newton8989

300
1
4
22

score -4 · Answer 6 · 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

29,388
11
94
103

answered Jul 01 '19 at 08:03

Shweta Verma

9
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

6 Answers6

Linked