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

Source: Internet
Author: User

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.



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.


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)



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 ');


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.