How to handle Single Quotes in Pl/SQL?

[like_to_learn]

hai,

My requirement is given below.
In my application one LOV is there to show Insured names.In that if we query for the name like ahemd's% then it is showing wrong(junk) data..................So,my question is how to add single quote to already existing single quote.........................I tried in many ways but I didn't get any solutions for this...............even I used Q'[]' frunction but no use.................................One more thing,we need to query for that name dynamically means.............

select '&name' from dual;
where '&name' -- you can pass as ahemd's..................but once you give this you will get a eror message like
"ORA-01756: quoted string not properly terminated".please send me solution..........................

[debasisdas]

you need to use REPLACE function to work around single quote.

[like_to_learn]

How can I use Replace function?
I am passing the sting dynamically..............
ex:
SELECT REPLACE('&NAME',''',''''') FROM DUAL;
if you run this statement,it will ask the name
then you just enter adem's.....
If you execute that statement you will get
"ORA-01756: quoted string not properly terminated" error..........
please suggest me, is there any way to handle.................

[irukulapati]

I don't think you are still looking for this answer.But I thought I would post answer so that it would be helpful who are following this thread.

assuming that you are trying to eliminate single quote.

SELECT REPLACE('&NAME','''','') FROM DUAL

pass 2 singlequotes in the parameter for adem's

ex:adem''s

would return adems

OR
from the given table

SELECT REPLACE(col1,'''','') FROM TEMP
where col1='adem''s'

The actual data in the column reads as adem's

[amdarden]

I am trying to solve a similar issue. We need to find all the names that have an apostrophe in it. I tried the code below, but I get the error below the code. Help?

use enterprise
SELECT REPLACE('&fldName_Last','''','') FROM locStaff
where fldName_Last like "'%"

Msg 207, Level 16, State 1, Line 4
Invalid column name ''%'.

I don't think you are still looking for this answer.But I thought I would post answer so that it would be helpful who are following this thread.

assuming that you are trying to eliminate single quote.

SELECT REPLACE('&NAME','''','') FROM DUAL

pass 2 singlequotes in the parameter for adem's

ex:adem''s

would return adems

OR from the given table

SELECT REPLACE(col1,'''','') FROM TEMP
where col1='adem''s'

The actual data in the column reads as adem's

[ubecdaniweb]

select replace(q'[&test]','''','') from dual

[pratik_garg]

same way,
you can use q'[] function here to solve your problem.
as given by ubecdaniweb

select replace(q'[&test]','''','') from dual;-- for remove wuote from test variable

but, as you questioned, you want to add one more single quote here so you can use following query..

select replace(q'[&test]','''','''''') from dual;