Shulou(Shulou.com)05/31 Report--

This article mainly introduces the oracle single quotation mark escape example analysis, has a certain reference value, interested friends can refer to, I hope you read this article after a lot of gains, the following let Xiaobian take you to understand.

ORACLE Single Quotation Escape:

In ORACLE, single quotes have two roles:

1: String is quoted by single quotes

2: Escape.

The use of single quotation marks is the principle of proximity. It's harder to understand when single quotes act as escape characters.

1. Starting with the second single quote is considered an escape character if there is a single quote (even if there is only one) after the second single quote.

SQL> select '''' result from dual; RESULT ------ '

The second single quote is used as an escape character, and the third single quote is escaped. SQL can be better understood as follows:

select ' '' ' from dual; output:' //

2. connector '||'leads to a new round of escapes:

connection symbol '||'The single quotes around have nothing to do with anything,

unless '||'is as part of a string (this is common in dynamic SQL).

SQL> select 'name'||'''' result from dual; RESULT ------ name'

Understanding:|| The next one is a string, which is preceded and followed by ', and in the middle is a dense single quote string'', so the first one is an escape function.

SQL> select 'name''''' result from dual; RESULT ------ name''

The first and last single quotes are used as string references, and the middle four single quotes escape in pairs.

//

Use double quotes in oracle:

1)In general, a double quote in a string is treated as just an ordinary character.

Double quotes do not need to appear in pairs:

SQL> select 'hh34"hours""mi""minutes""ss" seconds "' results from dual; RESULTS ---------------- hh34" hours ""mi"" minutes ""ss"seconds" //

2)When appearing in the format string of to_char, double quotes have a special effect, which is to wrap illegal formatting characters to avoid ORA-01821: date format not recognized error. That is, after removing the double quotes and the characters they contain, what remains should be a legally formatted string.

to_char ignores double quotes when processing format strings:

SQL> select to_char(sysdate,'hh34"hr"mi"min"ss"sec"') results from dual; RESULTS ---------- 06 hr 39 min 51 s//

The format string is 'hh34' hour 'mi' minute 'ss' second '; after removing the double quotes, what remains is'hh34miss'which is a legal format string.

However, even if you really don't know the usage of double quotation marks, you can also handle it like this, although it is troublesome:

select to_char(sysdate, 'hh34') ||'hour'|| to_char(sysdate, 'mi') ||'minute'|| to_char(sysdate, 'ss') ||'seconds' AS RESULT from dual; RESULT ------------- 06 h 40 min 18 s

example

create table t_test1(str_sql varchar2(2000));create table t_test2(str_sql varchar2(2000),cnt number(10));truncate table t_test1;truncate table t_test2;insert into t_test1 select table_name from user_tables;commit;--------set serveropput on;declarevv_name varchar2(200):='';vv_cnt number(10):=0;vv_sql varchar2(2000):='';begin for usr in (select str_sql from t_test1) loop vv_sql := rtrim('insert into t_test2 SELECT '''||usr.str_sql|| ''',COUNT(1) FROM ' || usr.str_sql,';'); dbms_output.put_line(vv_sql); execute immediate vv_sql; commit; end loop; commit;end; vv_sql := rtrim('insert into t_test2 SELECT '''||usr.str_sql|| ''',COUNT(1) FROM ' || usr.str_sql,';');rtrim(a,b):

Delete the b string of the a string from the right;

The above example is: delete 'insert into t_test2 SELECT'|| usr.str_sql|| ''',COUNT(1) FROM ' ||usr.str_sql;

'insert into t_test2 SELECT '''||usr.str_sql|| ''',COUNT(1) FROM ' || usr.str_sql 'insert into t_test2 SELECT '''1 234==> insert into t_test2 SELECT '

The 1st and 4th single quotation marks represent the string inside, the 2nd single quotation mark is the escape character, and the 3rd single quotation mark is the single quotation mark you want to output;

|| --Connection string usr.str_sql||--Connection string ''', COUNT(1) FROM '123 4

The first and fourth single quotes represent the string inside, the second single quote is the escape character, and the third single quote is the single quote you want to output;

==> ',COUNT(1) FROM ||--Connection string usr.str_sql

The above statement is equivalent to

==> insert into t_test2 SELECT 'usr.str_sql',COUNT(1) FROM usr.str_sql Thank you for reading this article carefully, I hope Xiaobian shared "oracle single quotation escape example analysis" This article is helpful to everyone, but also hope that everyone more support, pay attention to the industry information channel, more relevant knowledge waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.