Single quotes and double quotes in Oracle

 

In Oracle:

 

Double quotation marks are used to indicate that when an object is created, the object name and field name are added with double quotation marks, indicating that Oracle will be case-sensitive. Otherwise, each else will be capitalized by default.

 

The single quotation marks indicate that the field with single quotation marks is a string similar to a word and is case-insensitive.

 

Single quotes are used to identify the differences between characters and numbers.

 

When a string text is specified, the string text must be enclosed in single quotes.

 

Before Oracle10g, if the string text contains single quotes, you must use two single quotes. For example: I'm a String!

 

The String text is: string_var: = 'I'm a String! '

 

Other delimiters (<>, [], {}, etc.) can be used in oracle10g. Note that when using these delimiters, you must not only add single quotation marks before and after the delimiters, but also contain the prefix q. example: string_var: = Q' [I'm a String!] ';

 

For example

 

Select * from table_name where id = 1; this indicates that the queried number is

 

Select * from table_name where id = '1 ';

 

Suppose that the double quotation marks are like this. If you have a table field named sysdate, because sysdate is a special character in oracle, when you want to query this field

 

Select "sysdate" from table_name;

 

But if you use

 

Select 'sysdate' from table_name;

 

The result is sysdate.

 

The following update statement cannot be used: update device set dept = 'select * from message where name = 'Tom '';

 

Handling essentials:

 

Update device set dept = 'select * from message where name = ''tom ''where id = 3 ';

 

Execution successful.

 

It turns out that two ''indicate one'

 

 

 

Single quotes have three identities in Oracle:

 

 

 

1. Used to reference A String constant, that is, to define the start and end of a string

 

2. Escape Character, escape the character (single quotation mark) that follows it

 

3. It represents itself, that is, it appears in a String constant as part of a string.

 

 

It is often confusing when single quotes with different identities appear next to each other in SQL statements. I will base on my own experiences, it uses examples to show you how to understand the meaning of each single quotation mark.

 

 

 

Conclusion:

 

1. The meaning of the two single quotes that appear at the beginning and end of the expression must be that a string is referenced to define the start and end of the string.

 

2. if the single quotation marks appear in the middle of the expression (that is, the single quotation marks starting and ending with a non-expression), and there are no other characters between the multiple single quotation marks, when we analyze from left to right, the first character in the first pair of single quotes is an escape character. It escapes the second single quotes that follow it to make the second single quotes appear as a character in a String constant. Second, third, and so on ......

 

 

The following is an example of your experience:

 

Example 1:

 

 

 

Select ''' from dual;

 

Result'

 

In the '''' clause, 1st and 4 single quotes indicate that a String constant is referenced. The rest of the expression is analyzed from left to right, because the middle is a pair of single quotes, therefore, the first character in the pair is an escape character, which escapes the second character so that the second character represents the single quotation mark itself. The analysis shows that there is a String constant, with only one single quotation mark character. The running results of SQL statements are consistent with our analysis results.

 

 

 

Example 2:

 

Select 'exit ''' from dual

 

Result exit''

 

 

 

In the expression 'exit ''', the first and last single quotes indicate that a String constant is referenced. The rest of the expression is analyzed from left to right. In the remaining part, the first single quotation mark is followed by a single quotation mark. We use it as a pair. According to Example 1, the parsing result of this pair is a single quotation mark character. Then, we continue to go to the right, we also encounter a pair of single quotes that are next to each other. Similarly, their parsing results are also a single quotes character. The analysis shows that the String constant here contains exit and two single quotes. The running results of SQL statements are consistent with our analysis results.