SQL MID 函数

一、SQL MID 函数 语法

SQL语言中的 MID 字符串函数显示来自原始字符串的特定索引值的字符或子字符串。SQL 还允许您对表使用 MID 函数。MID 函数类似于 SUBSTR 字符串函数。

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

SELECT MID(Column_Name, Starting_Index_value, Number_of_Characters) AS Alias_Name FROM Table_Name;

在这种语法中，我们必须定义要在其上执行 MID() 函数的列的名称。在这里， Number_of_Characters 参数是可选的。如果省略，则此函数从起始索引值中提取整个字符串。

语法2：此语法使用带有字符串的 MID 函数：

SELECT MID(Original_String, Starting_Index_value, Number_of_Characters);

语法3：此语法使用带有单个字符的 MID 函数：

SELECT MID(String, Starting_Index_value, 1);

二、SQL MID 函数 示例

示例 1：以下 SELECT 查询显示给定字符串第 17 位的字符

SELECT MID(  'Yiidian is a website for professionals', 17, 24);

此 SQL 查询返回字符串中第 17 位之后的 24 个带空格的字符。

输出结果为：

website for professionals

示例 2：以下 SELECT 查询显示给定字符串第 -17位的字符：

SELECT MID(  'Yiidian is a website for professionals', -17, 5);

此 SQL 查询显示字符串最后 17个位置的五个字符。

website for professionals

示例 3：以下 SELECT 查询显示字符串第 5个位置的所有字符。

SELECT MID(  'New Delhi IS the Capital OF India', 5);

输出结果为：

Delhi IS the Capital OF India

示例 4：以下 SELECT 查询显示字符串第 6个位置的单个字符。

SELECT MID( 'Yiidian', 6, 1);

输出结果为：

n

示例 5：此示例将 MID 函数与 SQL 表一起使用

在此示例中，我们将创建一个要在其上执行 MID 函数的新表。

CREATE TABLE table_name
(
First_Column_of_table Data Type (character_size of 1st Column),  
Second_Column_of_table Data Type (character_size of the 2nd column ),  
Third_Column_of_table Data Type (character_size of the 3rd column),  
...  

Last_Column_of_table Data Type (character_size of the Nth column)
);  

以下 CREATE 语句创建Schoolboy_Marks表：

CREATE TABLE Schoolboy_Marks
(
Schoolboy_ID INT NOT NULL PRIMARY KEY,  
Schoolboy_First_Name VARCHAR (100),  
Schoolboy_Middle_Name VARCHAR (100),  
Schoolboy_Last_Name VARCHAR (100), 
Schoolboy_Class INT NOT NULL,
Schoolboy_City Varchar(120),
Schoolboy_State Varchar (80),
Schoolboy_Marks INT 
);

下面的 INSERT 语句在Schoolboy_Marks表中插入大学 Faculties 的记录：

INSERT INTO Schoolboy_Marks 
(Schoolboy_ID, Schoolboy_First_Name, Schoolboy_Middle_Name, Schoolboy_Last_Name, Schoolboy_Class, Schoolboy_City, Schoolboy_State, Schoolboy_Marks) 
VALUES ( 4002, Vishal, Gurr, Sharma, 8, Murthal, Haryana, 95 );

INSERT INTO Schoolboy_Marks 
(Schoolboy_ID, Schoolboy_First_Name, Schoolboy_Middle_Name, Schoolboy_Last_Name, Schoolboy_Class, Schoolboy_City, Schoolboy_State, Schoolboy_Marks) 
VALUES (4007, Raj, singhania, Gupta, 6, Ghaziabad, Uttar Pradesh, 91);

INSERT INTO Schoolboy_Marks 
(Schoolboy_ID, Schoolboy_First_Name, Schoolboy_Middle_Name Schoolboy_Last_Name, Schoolboy_Class, Schoolboy_City, Schoolboy_State, Schoolboy_Marks)  
VALUES (4004, Yash, Chopra, Singhania, 9, Jaipur, Rajasthan, 85);

INSERT INTO Schoolboy_Marks 
(Schoolboy_ID, Schoolboy_First_Name, Schoolboy_Middle_Name, Schoolboy_Last_Name, Schoolboy_Class, Schoolboy_City, Schoolboy_State, Schoolboy_Marks) 
VALUES (4011, Vinay, Sharma, Roy, 8, Chandigarh, Punjab, 94);

INSERT INTO Schoolboy_Marks 
(Schoolboy_ID, Schoolboy_First_Name, Schoolboy_Middle_Name, Schoolboy_Last_Name, Schoolboy_Class, Schoolboy_City, Schoolboy_State, Schoolboy_Marks) 
VALUES (4006, Manoj, singhania, Gupta, 5, Ghaziabad, Uttar Pradesh, 83);


INSERT INTO Schoolboy_Marks 
(Schoolboy_ID, Schoolboy_First_Name, Schoolboy_Middle_Name, Schoolboy_Last_Name, Schoolboy_Class, Schoolboy_City, Schoolboy_State, Schoolboy_Marks) 
VALUES (4010, Ram, Raheem, Gupta, 9, Lucknow, Uttar Pradesh, 89);

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

SELECT * FROM Schoolboy_Marks; 

输出结果为：

Schoolboy_Id	Schoolboy_First_Name	Schoolboy_Middle_Name	Schoolboy_Last_Name	Schoolboy_Class	Schoolboy_City	Schoolboy_State	Schoolboy_Marks
4001	Aman	Roy	Sharma	4	Chandigarh	Punjab	88
4002	Vishal	Gurr	Sharma	8	Murthal	Haryana	95
4007	Raj	singhania	Gupta	6	Ghaziabad	Uttar Pradesh	91
4004	Yash	Chopra	Singhania	9	Jaipur	Rajasthan	85
4011	Vinay	Sharma	Roy	8	Chandigarh	Punjab	94
4006	Manoj	Singhania	Gupta	5	Ghaziabad	Uttar Pradesh	83
4010	Ram	Raheem	Gupta	9	Lucknow	Uttar Pradesh	89

查询 1：以下 SELECT 查询将 MID 函数与上述 Schoolboy_Marks 表的 Schoolboy_Last_Name 列一起使用：

SELECT Schoolboy_Last_Name, MID(Schoolboy_Last_Name, 2, 4) AS MID_2_4 FROM Schoolboy_Marks;

此 SQL 语句显示每个学生的 La​​st name 的第二个位置之后的四个字符。

输出结果为：

Schoolboy_Last_Name	MID_2_4
Sharma	harm
Sharma	harm
Gupta	upta
Singhania	ingh
Roy	oy
Gupta	upta
Gupta	upta

查询 2：以下 SELECT 查询将 MID 函数与上述 Schoolboy_Marks 表的 Schoolboy_Last_Name 列一起使用：

SELECT Schoolboy_Last_Name, MID(Schoolboy_Last_Name, -3, 2) AS MID_-3_2 FROM Schoolboy_Marks;