SQL 32-SQL statement generated by the concatenate function in Excel

When you get an Excel file, you need to insert the data into the database. In addition to using SSIS data import, you can also use the concatenate function in Excel, the first use may confuse you. It is not difficult to understand the definition of this function.

 

Definition

The concatenate function combines up to 255 text strings into one text string. Join items can be text, numbers, cell references, or a combination of these items. For example, if cell A1 of your worksheet contains the name of a person and cell B1 contains the last name of the person, you can use the following formula to merge these two values into another cell: = concatenate (A1, "", B1) The second parameter ("") in this example is a space character. You must specify any space or punctuation that you want to display in the result as a parameter enclosed by double quotation marks.

 

Syntax
Concatenate (text1, [text2],...) the concatenate function syntax has the following parameters (values that provide information for an operation, event, method, attribute, function, or process .) :
Text1 is required. The first text item to be connected.
Text2,... optional. Other text items, up to 255. Items must be separated by commas.

Note: You can also use the Concatenation symbol (&) calculation operator instead of the concatenate function to connect text items. For example, = A1 & B1 returns the same value = concatenate (A1, B1)

 

Example

The Excel content is as follows:

Nsbm Mc ugly
001 A1 Xiao Li
002 A2 Wang
003 A3 John
004 A4 S4.

First, let's look at the following expression:

= Concatenate ("insert into daoru (nsbm, MC, ugly) values ('", A2, "', '", B2, "', '", C2, "');")

First, this expression has a "=" before it, then the expression name concatenate (), and finally its parameter. This part is the most complicated.

First parameter: "insert into daoru (nsbm, MC, ugly) values ('" This is a string

The second parameter is A2, which is a cell reference.

The third parameter: "','" is a string.

Fourth parameter: B2. this is a cell reference.

Fifth parameter: "','" is a string

The sixth parameter is C2, which is a cell reference.

The seventh parameter: "');" is a string.

The generated statement is as follows:

Insert into daoru (nsbm, MC, ugly) values ('001', 'a1', 'lily ');
Insert into daoru (nsbm, MC, ugly) values ('002 ', 'a2', 'wang ');
Insert into daoru (nsbm, MC, ugly) values ('003 ', 'a3', 'small three ');
Insert into daoru (nsbm, MC, ugly) values ('004 ', 'a4', '4 ');