CODEWITHSUNDEEP
sqlpostgresqlyiisyntax-error
Neeraj
Neeraj

Reputation: 702

Postgres syntax error when using 'like' in single quote

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.

Upvotes: 0

Views: 3117

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 247950

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

Upvotes: 1

Neeraj
Neeraj

Reputation: 702

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 312219

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

Upvotes: 1

Related Questions