Oracle single quotes and double roles-string reference and escape

Oracle single quotes dual role-string reference and escape in ORACLE, single quotes have two roles, one is the string is referenced by single quotes, the other is escape, when learning to dynamically constitute the SQL is often puzzled. The use of single quotes is proximity matching, that is, proximity principle. It is not easy to understand when single quotes act as escape roles. 1. It is regarded as an escape character starting from the second single quotation mark. If the second single quotation mark is followed by a single quotation mark (even if there is only one ). SQL> SELECT ''' FROM DUAL; ''-- 'to verify that the result is the third single quotation mark escaped by the second single quotation mark (SELECT ''' FROM DUAL ;), we do the following two tests: SQL> SELECT ''' FROM DUAL; ERROR: ORA-01756: the strings in the brackets are not properly ended. I believe everyone is familiar with such errors. SQL> SELECT 'sdlf FROM DUAL; ERROR: ORA-01756: the strings in the brackets are not properly ended. That is to say, when the second single quotation mark acts as an escape role, the third single quotation mark is escaped. (select ''' from dual;) Naturally, the single quotation mark that matches the first single quotation mark is missing, the two experiments under the isolated single quotes are more supportive of the above conclusion. SQL> SELECT ''' from dual; ''' ------ 'SQL> SELECT ''' FROM DUAL; SELECT ''' FROM DUAL * ERROR: ORA-00923: The expected FROM keyword is not found for the first: SELECT ''' from dual; for the second: Escape does not exist. The above two experiments are actually a single quotation mark escape and a non-escape nature: the escape is dense, that is, if the single quotation mark appears at the escape position, the single quotation mark follows closely (it is defined as follows: there is no distance between two single quotation marks). In this case, the single quotation mark does not act as an escape character, but is paired with it. 2. the connector '|' leads to a new round of escape: Connection Symbol '|' there is no relationship between the left and right single quotes, unless '|' is a part of a string (which is common in dynamic SQL ). SQL> SELECT 'oracle '| ''' FROM DUAL; 'oracle' | ''' ---------- ORACLE 'personal understanding, 'oracle '| ''' the "'''" after it should be considered as a string (that is, a single quotation mark before and after it, and a string of "'' "in the middle, the middle is dense single quotes, so the first is the escape Function) SQL> SELECT 'oracle ''' FROM DUAL; 'oracle ''' ------------ ORACLE ''' for the first one, the first two single quotes are paired, and the last four single quotes are allocated according to the first principle above, that is: SELECT 'oracle '| ''' from dual; for the second, because there is a single quotation mark after the second single quotation mark, it is not paired with the first one, but acts as an escape role. BOTH: SELECT 'oracle ''' from dual; with the above two principles, we can easily face complex dynamic SQL statements. In MySQL, you can use the concat function to add single quotes, as shown below: mysql> select * from t1; + ------ + | a | B | + ------ + | 1 | 2 | 2 | 3 | + ------ + 2 rows in set (0.00 sec) mysql> select concat (''', a) from t1; + -------------- + | concat (''',) | + ---------------- + | '1 | '2 | + ---------------- + 2 rows in set (0.00 sec) mysql> select concat (''', ), '''') from t1; + ----------------------------- + | concat (''', ),'''') | + --------------------------- + | '1' | '2' | + ------------------------------- + 2 rows in set (0.00 sec)