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.
sql postgresql yii syntax-error
edited Nov 13 '18 at 7:14
Laurenz Albe
46.1k102748
asked Nov 13 '18 at 6:02
NeerajNeeraj
12411
add a comment |
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.
sql postgresql yii syntax-error
edited Nov 13 '18 at 7:14
Laurenz Albe
46.1k102748
asked Nov 13 '18 at 6:02
NeerajNeeraj
12411
So where your query? Just show the error not helping..
– dwir182
Nov 13 '18 at 6:35
1
'%Women and Children''s Voices%'. Or possibly a backslash instead of doubled quotes; I don't remember which one postgres prefers.
– Shawn
Nov 13 '18 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
Nov 13 '18 at 6:51
add a comment |
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.
sql postgresql yii syntax-error
edited Nov 13 '18 at 7:14
Laurenz Albe
46.1k102748
asked Nov 13 '18 at 6:02
NeerajNeeraj
12411
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.
sql postgresql yii syntax-error
sql postgresql yii syntax-error
edited Nov 13 '18 at 7:14
Laurenz Albe
46.1k102748
asked Nov 13 '18 at 6:02
NeerajNeeraj
12411
edited Nov 13 '18 at 7:14
Laurenz Albe
46.1k102748
asked Nov 13 '18 at 6:02
NeerajNeeraj
12411
edited Nov 13 '18 at 7:14
Laurenz Albe
46.1k102748
edited Nov 13 '18 at 7:14
Laurenz Albe
46.1k102748
edited Nov 13 '18 at 7:14
Laurenz Albe
46.1k102748
46.1k102748
asked Nov 13 '18 at 6:02
NeerajNeeraj
12411
asked Nov 13 '18 at 6:02
NeerajNeeraj
12411
asked Nov 13 '18 at 6:02
NeerajNeeraj
12411
12411
So where your query? Just show the error not helping..
– dwir182
Nov 13 '18 at 6:35
1
'%Women and Children''s Voices%'. Or possibly a backslash instead of doubled quotes; I don't remember which one postgres prefers.
– Shawn
Nov 13 '18 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
Nov 13 '18 at 6:51
add a comment |
So where your query? Just show the error not helping..
– dwir182
Nov 13 '18 at 6:35
1
'%Women and Children''s Voices%'. Or possibly a backslash instead of doubled quotes; I don't remember which one postgres prefers.
– Shawn
Nov 13 '18 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
Nov 13 '18 at 6:51
So where your query? Just show the error not helping..
– dwir182
Nov 13 '18 at 6:35
So where your query? Just show the error not helping..
– dwir182
Nov 13 '18 at 6:35
1
1
'%Women and Children''s Voices%'. Or possibly a backslash instead of doubled quotes; I don't remember which one postgres prefers.– Shawn
Nov 13 '18 at 6:48
'%Women and Children''s Voices%'. Or possibly a backslash instead of doubled quotes; I don't remember which one postgres prefers.– Shawn
Nov 13 '18 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
Nov 13 '18 at 6:51
Hi @Shawn, thanks for your reply, I did same thing and it works, I replaced single quote with double and it works
– Neeraj
Nov 13 '18 at 6:51
add a comment |
3 Answers
3
active
oldest
votes
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 '18 at 6:52
MureinikMureinik
182k22133200
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.
– Neeraj
Nov 13 '18 at 7:00
add a comment |
You should use the format function to construct the SQL statement, using the %L placeholder for the pattern.
answered Nov 13 '18 at 7:12
Laurenz AlbeLaurenz Albe
46.1k102748
add a comment |
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 '18 at 6:55
NeerajNeeraj
12411
Why are you not using placeholders? Doing this yourself is madness.
– mu is too short
Nov 13 '18 at 18:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53274731%2fpostgres-syntax-error-when-using-like-in-single-quote%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 6:52
MureinikMureinik
182k22133200
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.
– Neeraj
Nov 13 '18 at 7:00
add a comment |
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 '18 at 6:52
MureinikMureinik
182k22133200
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.
– Neeraj
Nov 13 '18 at 7:00
add a comment |
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 '18 at 6:52
MureinikMureinik
182k22133200
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 '18 at 6:52
MureinikMureinik
182k22133200
answered Nov 13 '18 at 6:52
MureinikMureinik
182k22133200
answered Nov 13 '18 at 6:52
MureinikMureinik
182k22133200
answered Nov 13 '18 at 6:52
MureinikMureinik
182k22133200
182k22133200
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.
– Neeraj
Nov 13 '18 at 7:00
add a comment |
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.
– Neeraj
Nov 13 '18 at 7:00
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.
– Neeraj
Nov 13 '18 at 7:00
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.
– Neeraj
Nov 13 '18 at 7:00
add a comment |
You should use the format function to construct the SQL statement, using the %L placeholder for the pattern.
answered Nov 13 '18 at 7:12
Laurenz AlbeLaurenz Albe
46.1k102748
add a comment |
You should use the format function to construct the SQL statement, using the %L placeholder for the pattern.
answered Nov 13 '18 at 7:12
Laurenz AlbeLaurenz Albe
46.1k102748
add a comment |
You should use the format function to construct the SQL statement, using the %L placeholder for the pattern.
answered Nov 13 '18 at 7:12
Laurenz AlbeLaurenz Albe
46.1k102748
You should use the format function to construct the SQL statement, using the %L placeholder for the pattern.
answered Nov 13 '18 at 7:12
Laurenz AlbeLaurenz Albe
46.1k102748
answered Nov 13 '18 at 7:12
Laurenz AlbeLaurenz Albe
46.1k102748
answered Nov 13 '18 at 7:12
Laurenz AlbeLaurenz Albe
46.1k102748
answered Nov 13 '18 at 7:12
Laurenz AlbeLaurenz Albe
46.1k102748
46.1k102748
add a comment |
add a comment |
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 '18 at 6:55
NeerajNeeraj
12411
Why are you not using placeholders? Doing this yourself is madness.
– mu is too short
Nov 13 '18 at 18:14
add a comment |
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 '18 at 6:55
NeerajNeeraj
12411
Why are you not using placeholders? Doing this yourself is madness.
– mu is too short
Nov 13 '18 at 18:14
add a comment |
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 '18 at 6:55
NeerajNeeraj
12411
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 '18 at 6:55
NeerajNeeraj
12411
answered Nov 13 '18 at 6:55
NeerajNeeraj
12411
answered Nov 13 '18 at 6:55
NeerajNeeraj
12411
answered Nov 13 '18 at 6:55
NeerajNeeraj
12411
12411
Why are you not using placeholders? Doing this yourself is madness.
– mu is too short
Nov 13 '18 at 18:14
add a comment |
Why are you not using placeholders? Doing this yourself is madness.
– mu is too short
Nov 13 '18 at 18:14
Why are you not using placeholders? Doing this yourself is madness.
– mu is too short
Nov 13 '18 at 18:14
Why are you not using placeholders? Doing this yourself is madness.
– mu is too short
Nov 13 '18 at 18:14
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53274731%2fpostgres-syntax-error-when-using-like-in-single-quote%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So where your query? Just show the error not helping..
– dwir182
Nov 13 '18 at 6:35
1
'%Women and Children''s Voices%'. Or possibly a backslash instead of doubled quotes; I don't remember which one postgres prefers.– Shawn
Nov 13 '18 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
Nov 13 '18 at 6:51