SQL INSERT 函数

一、SQL INSERT 函数 语法

SQL语言的 INSERT 字符串功能允许您在给定字符串中的固定位置插入字符串或字符。

语法1：此语法使用带有 SQL 表列名的 INSERT 函数：

SELECT INSERT (Column_Name, Position, Number, Sub-string)  AS  Alias_Name  FROM  Table_Name;

这种语法中，我们必须 指定要修改其值的列的名称。

语法 2：此语法使用 带有 给定字符串的  INSERT函数：

SELECT INSERT (String, Position, Number, Sub-String)  AS  Alias_Name;

二、SQL INSERT 函数 示例

示例 1：以下 SELECT 查询将 “Tpoint” 字符串 插入到 “  Java Excel”字符串的 第五个位置  ：

SELECT INSERT ( 'Java Excel' , 5, 6,  'Tpoint' )  AS  Tpoint_5_6;   

输出结果为：

Tpoint_5_6

Java Tpoint

示例 2：以下 SELECT 查询在给定单词的第一个位置插入“C”字符：

SELECT INSERT('ongratulations', 1, 1, 'C') AS C_1_1;  

输出结果为：

C_1_1

Cngratulations

示例 3：以下 SELECT 查询在 'of' 单词之前插入 'Capital' 子字符串：

SELECT INSERT('New Delhi is the of India', 17, 8, 'Capital ') AS Capital_17_8;

输出结果为：

Capital_17_7

New Delhi is the Capital

示例 4：以下 SELECT 查询在“Java Excel”字符串的第五个位置插入“Tpoint”字符串：

SELECT INSERT('Yiidian.com', 1, 7, 'Example') AS Example_1_10;

输出结果为：

Capital_17_7

Example.com

示例 5：此示例对结构化查询语言中的表使用 INSERT 函数。

现在，我们在 SQL 中创建新表，这有助于理解每个字符串函数。在 SQL 数据库中创建新表的语法如下：

CREATE TABLE table_name  
(  
First_Column_of_table Data Type (character_size of First Column),    
Second_Column_of_table Data Type (character_size of the Second column ),    
Third_Column_of_table Data Type (character_size of the Third column),    
...    
  
Last_Column_of_table Data Type (character_size of the Last column)  
);    

以下 CREATE 语句创建Worker_Grade表：

CREATE TABLE Worker_Grade  
(  
Worder_ID Varchar(50) PRIMARY KEY,    
First_Name VARCHAR (100),    
Last_Name VARCHAR (100),   
First_City Varchar(120),  
Second_City Varchar(120),  
New_City Varchar(120),  
Attendance_Remarks Varchar(50),   
Worker_Salary Varchar(50),   
Grade Varchar (80)  
);  

下面的 INSERT 语句在Worker_Grade表中插入 Workers with Grades、Salaries 和 Remarks 的记录：

INSERT INTO Worker_Grade (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade) VALUES (10, Aman, Sharma, Lucknow Chandigarh, Ghaziabad, 88, 9598, A2);  
  
INSERT INTO Worker_Grade   
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   
VALUES ( 02, Vishal, Sharma, Chandigarh, Ghaziabad, Delhi, 95, 8200, A1 );  
  
INSERT INTO Worker_Grade   
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   
VALUES (07, Raj, Gupta, Delhi, Ghaziabad, Lucknow, 91, 9512, A1);  
  
INSERT INTO Worker_Grade   
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)    
VALUES (04, Yash, Singhania, Ghaziabad, Delhi, Lucknow, 85, 8225, A2);  
  
INSERT INTO Worker_Grade   
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   
VALUES (11, Vinay, Roy, Delhi, Kanpur, Ghaziabad, 95, 9712, A1);  
  
INSERT INTO Worker_Grade   
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   
VALUES (16, Manoj, Gupta, Ghaziabad, Meerut, Chandigarh, 95, 9025, B1);  
  
  
INSERT INTO Worker_Grade   
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   
VALUES (19, Ram, Gupta, Lucknow, Ghaziabad, Chandigarh, 89, 9512, A2);  

以下 SELECT 语句显示上述Worker_Grade表的插入记录：

SELECT * FROM Worker_Grade;  

输出结果为：

Worder_ID	First_Name	Last_Name	First_City	Second_City	New_City	Attendance_Remarks	Work_Remarks	Grade
10	Aman	Sharma	Lucknow	Chandigarh	Ghaziabad	88	95	A2
02	Vishal	Sharma	Chandigarh	Ghaziabad	Ghaziabad	95	82	A1
07	Raj	Gupta	Delhi	Ghaziabad	Lucknow	91	95	A1
04	Yash	Singhania	Ghaziabad	Delhi	Lucknow	85	82	A2
11	Vinay	Roy	Delhi	Kanpur	Ghaziabad	95	97	A1
16	Manoj	Gupta	Ghaziabad	Meerut	Chandigarh	95	90	B1
19	Ram	Gupta	Lucknow	Ghaziabad	Chandigarh	89	95	A2

以下 SELECT 查询将 FORMAT 函数与上述 Worker_Grade 表的 First_Name 列一起使用：

SELECT First_Name, INSERT(First_Name, 1, 5, 'Aman ') AS Aman_1_5 FROM Worker_Grade;  

此 SQL 语句在每个工作人员名字的第一个位置插入“Aman”子字符串

输出结果为：

First_Name	Aman_1_5
Amanres	Aman es
Vishalkush	Aman lkush
Rajesh	Aman h
Yashraj	Aman aj
Vinaybhatt	Aman bhatt
Manojjeeta	Aman jeeta
Ramesh	Aman h