How to escape single quote in a string?

Question

I Have a pl-sql procedure of the below type:

BEGIN
p_name_all  VARCHAR2(20000 CHAR) ;
 ... 
 ...
 FOR name_rec IN name_cur
 LOOP
 IF (p_name_all IS NULL)
 p_name_all := '//' || name_rec.NAME || '//';  
 select RTRIM('p_name_all','//') from dual;
 ....
 ....
END IF
END LOOP
END

When i try to execute the above pl-sql procedure , it crashed. So i found out that while looping through the names one of the name has a single quote which is why i was facing issue. In this case how to escape single quote.

name.rec is having name1, name2,name3 , ..... out of these one name is having single quotes and not sure which one. I want to escape single quote here.

no this is not duplicate my question is a bit different – Sindhu Choudary Jul 18 '19 at 04:14 — Sindhu Choudary, Jul 18 '19 at 04:14

score 0 · Answer 1 · answered Jul 17 '19 at 06:27

0

Add one more single quote with the quote in string like - > isn''t it?

answered Jul 17 '19 at 06:27

Sarfaraaz Ansari

51
1
4

There are multiple names which we are not aware of and we will loop through the names by assigning to a variable in that case how will you escape a single quote when you are not aware of the string. We need to escape single quote by using the variable p_name_all – Sindhu Choudary Jul 17 '19 at 07:00

Littlefoot · Answer 2 · 2019-07-17T12:36:53.287

Escape it with the q-quoting mechanism, such as

p_name_all := q'[blabla'''blabla]';

Or, in your example (which is, by the way, invalid as it misses the declare section, while select requires into clause):

DECLARE
   p_name_all  VARCHAR2 (20000 CHAR)
      := q'[Yun?i?e? Ki?ga?am//Birtaniya//Aupuni M?'? Hui P? 'ia//???????//Britania//Yun?ited Kingdam//???????? ??????//Egyesült Királyság//Aialand Ugwu//Bretland]';
   l_result    VARCHAR2 (20000 CHAR);
BEGIN
   SELECT RTRIM (p_name_all, '//') INTO l_result FROM DUAL;
END;

[EDIT]

Ummm ... no - I'm afraid you got it wrong. Single quote within the NAME shouldn't make any problems. Here's a working example which shows how you should have done it (at least, how I understood the problem):

SQL> create table test (col varchar2(20));

Table created.

SQL> insert into test values ('littlefoot');

1 row created.

SQL> insert into test values ('big''foot');

1 row created.

SQL> set serveroutput on
SQL> declare
  2    p_name_all varchar2(1000);
  3  begin
  4    for name_rec in (select col name from test) loop
  5      p_name_all := p_name_all || '//' || name_rec.name ;
  6    end loop;
  7
  8    p_name_all := rtrim(p_name_all, '//');
  9    dbms_output.put_line('Result: ' || p_name_all);
 10  end;
 11  /
Result: //littlefoot//big'foot

PL/SQL procedure successfully completed.

SQL>

i Had reframed my question – Sindhu Choudary Jul 17 '19 at 11:41 — Sindhu Choudary, Jul 17 '19 at 11:41
I've also edited my answer; have a look, please. – Littlefoot Jul 17 '19 at 12:37 — Littlefoot, Jul 17 '19 at 12:37

How to escape single quote in a string?

2 Answers2