Postgres syntax error when using 'like' in single quote

Question

I am getting a syntax error in a PostgreSQL query. I am working on a project developed in YII1, I am getting an error

CDbCommand failed to execute the SQL statement: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "s" LINE 1: ...OT NULL AND sub_heading like '%Women and Children's Voices%'.

As you can see above, I am using the like operator in single quotes, and in the string there is another single quote (Children's). So PostgreSQL is throwing me an error. Please provide me a solution to escape the string.

'%Women and Children''s Voices%'. Or possibly a backslash instead of doubled quotes; I don't remember which one postgres prefers. — Shawn
– Shawn, Commented Nov 13, 2018 at 6:48
Hi @Shawn, thanks for your reply, I did same thing and it works, I replaced single quote with double and it works — Neeraj
– Neeraj, Commented Nov 13, 2018 at 6:51

Mureinik · Accepted Answer · 2018-11-13 06:52:22Z

1

You can escape a single quote in a string by using another single quote (i.e., '' instead of '. Note that these are two ' characters, not a single " character):

sub_heading LIKE '%Women and Children''s Voices%'
-- Here -----------------------------^

answered Nov 13, 2018 at 6:52

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Neeraj Over a year ago

Hi @Mureinik, thanks for your answer, Actually value Women and Children was coming from a variable, so I can't change it in the query so I replaced the single quote with double quotes using PHP.

Laurenz Albe · Accepted Answer · 2018-11-13 07:12:45Z

1

You should use the format function to construct the SQL statement, using the %L placeholder for the pattern.

answered Nov 13, 2018 at 7:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Comments

Neeraj · Accepted Answer · 2018-11-13 06:55:50Z

0

I solved this problem by replacing the single quote with double quotes using PHP. Here is the code There is a variable $var with value Women and Children's Voices. I replace that single quote using the str_replace() function. $var = str_replace("'", "''", $var);

answered Nov 13, 2018 at 6:55

Neeraj

7021 gold badge10 silver badges19 bronze badges

1 Comment

mu is too short Over a year ago

Why are you not using placeholders? Doing this yourself is madness.

Collectives™ on Stack Overflow

Postgres syntax error when using 'like' in single quote

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related