SQL double quotes and single quotes

SQL double quotes and single quotes

Delimiters are used only for identifiers. Delimiters cannot be used for keywords, regardless of whether they are marked as reserved words in SQL Server.
Quoted identifiers are separated by double quotation marks ("):
SELECT * from ' blanks in Table Name '
Identifiers enclosed in parentheses are delimited by square brackets ([]):
With SELECT * FROM [blanks in Table Name] When QUOTED_IDENTIFIER is on, SQL Server follows the SQL-92 rule for the use of double quotes and single quotes (') in SQL statements: double quotes can only be used for delimited identifiers, which cannot be used to delimit strings. To maintain compatibility with existing applications, SQL Server does not fully enforce the rule. If the string does not exceed the length of the identifier, the string can be enclosed in double quotes. But this is not recommended. Single quotes must be used to contain strings and cannot be used to delimit identifiers. If the string contains single quotes, you need to add a single quote before the single quote: SELECT * from ' My Table '
WHERE "last Name" = ' O ' Brien '

When QUOTED_IDENTIFIER is off, SQL Server follows the following rules for the use of double quotes and single quotes:

Quotation marks cannot be used to delimit identifiers, but are parentheses as delimiters.
Single or double quotes can be used to contain strings.
If you use double quotes, embedded single quotes do not need to be represented by two single quotes:
SELECT * FROM [my Table]
WHERE [last Name] = "O ' Brien"

1 First look at one of the simplest SQL statements
Insert into users (username) values ("Xiao Wang")
Let's see, this is a standard SQL statement, because username is a text-type field, so the value of the field should be double quotes, which means that Xiao Wang is a string.

(2) But in ASP, we usually write
Strsql= "Insert into users (username) values (' Xiao Wang ')"
At this point, the double quotation marks indicate that the middle is a string. The single quotes on both sides of Xiao Wang are nested in quotation marks, so the inner quotes should be for single quotes.

(3) When actually inserted, because Xiao Wang is usually a variable obtained from a form, but because a variable cannot be written directly to a string, it must be concatenated with a connector & and a string, so write the following form:
Myusername=request.form ("username")
......
Strsql= Insert into users (username) VALUES (' & MyUserName & ')
Many people are confused at this time, why there are single quotes, and double quotes AH. A simple look at the above formula is actually made up of the following three parts:
String constants: Insert into Users (username) values (' "
String variable: MyUserName
String constants: "')"

It may be difficult to understand directly the formula above, and now we can see the value of the variable myusername. If Myusername= "Xiao Wang", substituting:
Strsql= Insert into users (username) VALUES (' & "Xiao Wang" & ")"
Then execute the concatenation operator in turn, and the result is:
Strsql= "Insert into users (username) values (' Xiao Wang ')"
This is the correct SQL statement mentioned in (2).

Here we can see clearly:
The 1th and 2 double quotes in the equation are matched to indicate that this is a string. 3rd, 4 double quotes matching, also means a string.
The single quotation mark in the formula is the single quotation mark before and after the small king of the text field value.

(4) There is only one field above, here is a case of two fields:
Strsql= Insert into users (Username,password) VALUES (' & MyUserName & ', ' & MyPassword & ')
Where MyUserName and MyPassword are variables.

Now if Myusername= "Xiao Wang", mypassword= "123456", substituting, the result is:
Strsql= "Insert into Users (Username,password) VALUES ('" & "Xiao Wang" & "', '" & "123456" & ")"
The connection operation is performed sequentially, with the result:
Strsql= "Insert into Users (Username,password) values (' Xiao Wang ', ' 123456 ')"

(5) Text-type field values are surrounded by quotes, date type plus #, numbers, logic on both sides without adding anything.
As the following age is a number, submit_date is a date type.
Strsql= Insert into users (username,password,age,submit_date) VALUES (' & MyUserName & ', ' & MyPassword & Amp "'," & Myage & ", #" & Mysubmit_date & "#)"
Note that this is where you add the corresponding symbol on either side of the field, or not. If Myusername= "Xiao Wang", mypassword= "123456", myage=20,mysubmit_date= "2004-4-1", substituting, the result is:
Strsql= Insert into users (username,password,age,submit_date) VALUES (' & Xiao Wang ' & ', ' & ' 123456 "&" ', "&am P & ", #" &
"2004-4-1" & "#)"
Here the 20 is very special, the operation, the first will automatically convert to a string, because here is the operation of the string. The results are:
Strsql= Insert into users (username,password,age,submit_date) VALUES (' & Xiao Wang ' & ', ' & ' 123456 "&" ', "&am P "&", # "&
"2004-4-1" & "#)"
The connection operation is performed sequentially, with the result:
Strsql= "Insert into Users (username,password,age,submit_date) values (' Xiao Wang ', ' 123456 ', #2004 -4-1#)"
This is exactly the standard SQL statement we need.

(1) & is a concatenation operator that can concatenate two strings into a single string. Such as
A= "abc" & "Def"
After performing the connection operation, a= "abcdef". Again as
A= "ABC" & TEMP
Note that temp here is a string variable, if temp= "def", then substituting the above equation, the result is:
A= "abc" & "Def"
The connection operation continues, resulting in a= "abcdef". Sometimes, there may be more than one concatenation operator, as long as the sequence is followed. Such as
A= "abc" & "Def" & "GH"
The result is a= "ABCDEFGH".

(2) about double quotes ". Be sure to enclose the string with double quotes, indicating that it is a string. The double quotes on both sides of the "ABC", "Def" and "GH" above indicate that this is a string.
Of course, the string variable name cannot be added with double quotes on either side, as mentioned in the A= "ABC" & TEMP is a variable,
So there is no double quotation mark on either side, and if you add double quotes to temp, it becomes a string constant "temp".

(3) About single quotes '. Why do you use single quotes? This is because if quotation marks are nested, the inner double quotation marks are changed to single quotes.
Here's an example:
A= "strings should have double quotes, such as" abc ", OR ..."
People look at this sentence, the original meaning is: the front and the most behind the "means that the middle is a string." The double quotation marks on both sides of "ABC" indicate that ABC is a string. But then there will be a mistake. Because the 1th double quote is actually matched with the 2nd double quote, it's not the 4th double quote that everyone wants.
So what to do, at this point you need to change the inner double quote "ABC" to single quotes, as follows:
A= "strings must be enclosed in double quotes, such as ' abc ', OR ..."
The following will be added:
1 The above mentioned symbols are in the English state, belong to the grammar will be used in the symbol. If it is a symbol in the Chinese state, it does not need to change. As
"A=," said Xiao Wang, "Let's Eat." "
The Chinese symbols do not participate in the grammar, so there is no need for change.
2 when quotation marks are nested, the inner quotation marks are typically changed to single quotes, but can also be changed to two double quotes, as follows:
A= "The string must be double quotes in English, such as" abc ", OR ..."

About double quotes, single quotes, and & in SQL statements

(1) First look at one of the simplest SQL statements
Insert into users (username) values ("Xiao Wang")
Let's see, this is a standard SQL statement, because username is a text-type field, so the value of the field should be double quotes, which means that Xiao Wang is a string.
(2) But in ASP, we usually write
Strsql= "Insert into users (username) values (' Xiao Wang ')"
At this point, the double quotation marks indicate that the middle is a string. The single quotes on both sides of Xiao Wang are nested in quotation marks, so the inner quotes should be for single quotes.
(3) When actually inserted, because Xiao Wang is usually a variable obtained from a form, but because a variable cannot be written directly to a string, it must be concatenated with a connector & and a string, so write the following form:
Myusername=request.form ("username")
......
Strsql= Insert into users (username) VALUES (' & MyUserName & ')
Many people are confused at this time, why there are single quotes, and double quotes AH. A simple look at the above formula is actually made up of the following three parts:
String constants: Insert into Users (username) values (' "
String variable: MyUserName
String constants: "')"
It may be difficult to understand directly the formula above, and now we can see the value of the variable myusername. If Myusername= "Xiao Wang", substituting:
Strsql= Insert into users (username) VALUES (' & "Xiao Wang" & ")"
Then execute the concatenation operator in turn, and the result is:
Strsql= "Insert into users (username) values (' Xiao Wang ')"
This is the correct SQL statement mentioned in (2). Here we can see: The 1th, 2 double quotes in the equation, which means it's a string. 3rd, 4 double quotes matching, also means a string. The single quotation mark in the formula is the single quotation mark before and after the small king of the text field value.
(4) There is only one field above, here is a case of two fields:
Strsql= Insert into users (Username,password) VALUES (' & MyUserName & ', ' "&
MyPassword & "')"
Where MyUserName and MyPassword are variables. Now if Myusername= "Xiao Wang", mypassword= "123456", substituting, the result is:
Strsql= "Insert into Users (Username,password) VALUES ('" & "Xiao Wang" & "', '" & "123456" & ")"
The connection operation is performed sequentially, with the result:
Strsql= "Insert into Users (Username,password) values (' Xiao Wang ', ' 123456 ')"
(5) Text-type field values are surrounded by quotes, date type plus #, numbers, logic on both sides without adding anything. As the following age is a number, submit_date is a date type.
Strsql= Insert into users (username,password,age,submit_date) VALUES (' & MyUserName & ', ' & MyPassword & Amp "'," & Myage & ", #" & Mysubmit_date & "#)"
Note that this is where you add the corresponding symbol on either side of the field, or not. If Myusername= "Xiao Wang", mypassword = "123456", myage=20,mysubmit_date= "2004-4-1", substituting, the result is:
Strsql= Insert into users (username,password,age,submit_date) VALUES (' & King ' & "', '" & "123456" & "", " & & ", #" & "2004-4-1" & "#"
Here the 20 is very special, the operation, the first will automatically convert to a string, because here is the operation of the string. The results are:
Strsql= Insert into users (username,password,age,submit_date) VALUES (' & Xiao Wang ' & ', ' & ' 123456 "&" ', "&am P "&", # "&" 2004-4-1 "&" #) "
Perform the join operation sequentially, and the result
Strsql= Insert into users (username,password,age,submit_date) values (' Xiao Wang ', ' 123456 ', #2004 -4-1#) "This is exactly the standard SQL statement we need.

About insert Strings

A lot of students in this area has been a problem, in fact, mainly because of data types and variables in the mischief. The following is a separate story, although the INSERT statement is the same, but the Select, Update, and DELETE statements are the same. If the following forms are available:
Mytabe
Field 1 Username String type (name)
Field 2 Age numeral type (ages)
Field 3 Birthday Date type (birthday)
Field 4 Marry Boolean (whether married, married to True, not married to false)
Field 5 leixing String type (type)

1. Insert String type
If you want to insert a person named Zhang Hong, because it is a string, you should add a single apostrophe to the name on both sides of the INSERT statement, such as:
Strsql= "Insert into MyTable (username) VALUES (' Zhang Hong ')"
If the name is now a variable thename, then write
Strsql= "Insert into MyTable (username) VALUES (' & thename & ')"
Here insert INTO mytable (username) VALUES (' is the part before Zhang Hong, thename is a string variable, ') is the part behind Zhang Hong. Replace the thename variable with Cheng Changgong, then connect the three segments with &, and it becomes
Strsql= "Insert into MyTable (username) VALUES (' Zhang Hong ')".
If you want to insert two fields, such as "Zhang", the type is "student"
Strsql= "Insert into MyTable (username,leixing) VALUES (' Zhang ', ' student ')"
If the name is now a variable thename, and the type is also a variable thetype, it is written as:
Strsql= "Insert into MyTable (username,leixing) VALUES (' &thename&", "&thetype &") "
As with the first example, the thename and Thetype are replaced, and the connectors are concatenated into the same string as the above.

2. Inserting a numeric type
If inserting a record of age 12, pay attention to the number without a single apostrophe:
Strsql= "Insert into mytable (age) VALUES (12)"
If the age is now a variable theage, then:
Strsql= Insert into mytable values ("& Theage &")
Here insert INTO mytable (age) VALUES (which is the previous section of 12, Theage is an aging variable,) is the back portion of 12. Replace the theage with the & connector to connect the three parts to the same character as above.

3. Insert Date Type
Date type is similar to string type, but the apostrophe is replaced with the # number. (However, you can also use a single apostrophe in an Access database tutorial)
Strsql= "Insert into MyTable (birthday) VALUES (#1980 -10-1#)"
If you change the date variable thedate
Strsql= "Insert into MyTable (birthday) VALUES (#" & Thedate & "#)"

4. Insert Boolean
Boolean and numeric similar: only two values true and false, such as:
Strsql= Insert into MyTable (marry) VALUES (True)
If you change to a Boolean variable Themarry
Strsql= "Insert into MyTable (birthday) VALUES (" & themarry& ")"
5. Comprehensive example
Insert a record with a name of Zhang Hong, age 12, date of birth of 1970-6-8
Strsql= "Insert into MyTable (username,age,birthday) VALUES (' Hong Zhang ', #1970 -6-8#)"
Careful attention to the formula: because the name is a string, so Zhang Hong on both sides added a single apostrophe; age is a number, so there is no extra apostrophe. If replaced with a string variable thename, a numeric variable theage, and a date variable thebirthday, it becomes:
Strsql= Insert into MyTable (username,age,birthday) VALUES (' & thename & ', ' & theage& ', # "& Thebirthday & "#)"
In short, replace the variable, and then connect to complete the same string as the top.

6. Little Tips
(1) Insert String type
Replace the following sentence title with a variable:
Strsql= "Insert into MyTable (username) VALUES (' Zhang Hong ')"
Step one: First erase Zhang, add two quotes to the original position
Strsql= "Insert into MyTable (username) VALUES ('" ")"
Step two: Add two connectors in the Middle &
Strsql= "Insert into MyTable (username) VALUES (' & & ')"
Step three: Write the variable between two connectors
Strsql= "Insert into MyTable (username) VALUES (' & thename & ')"

(2) Insert Numeric/Boolean
Replace the following sentence title with a variable:
Strsql= "Insert into mytable (age) VALUES (12)"
Step one: First erase 12, add two quotes to the original position.
Strsql= "Insert into MyTable (username) VALUES (" ")"
Step two: Add two connectors in the Middle &
Strsql= "Insert into MyTable (username) VALUES (" & & ")"
Step three: Write the variable between two connectors
Strsql= "Insert into MyTable (username) VALUES (" & Theage & ")"

(3) Insert Date type
Replace the following sentence title with a variable:
Strsql= "Insert into MyTable (birthday) VALUES (#1980 -10-1#)"
Step one: First erase the 1980-10-1, add two quotes to the original position
Strsql= "Insert into MyTable (username) VALUES (#" "#)"
Step two: Add two connectors in the Middle &
Strsql= "Insert into MyTable (username) VALUES (#" & & "#)"
Step three: Write the variable between two connectors
Strsql= "Insert into MyTable (username) VALUES (#" & Thedate & "#)"