SQL MID函数

结构化查询语言（SQL）中的MID字符串函数显示原始字符串的特定索引值之后的字符或子字符串。SQL还允许您在表中使用MID函数。MID函数类似于SUBSTR字符串函数。

MID字符串函数的语法

语法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);

语法2： 这个语法使用MID函数和一个单一字符：

SELECT MID(String, Starting_Index_value, 1);

MID字符串函数的例子

例子1： 以下SELECT查询显示给定字符串的第17个位置开始的字符。

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

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

输出：

website for professionals

示例2： 下面的SELECT查询显示给定字符串中从第-17个位置开始的字符： th

SELECT MID(  'JavaTpoint 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查询显示了字符串的第8个字符。

SELECT MID( 'JavaTpoint', 8, 1);

输出：

n

示例5：此示例使用了MID函数与SQL表

在此示例中，我们将创建一个新表，然后对其执行MID函数。

创建新SQL表的语法如下所示：

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

以下是创建 Schoolboy_Marks 表的 CREATE 语句：

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 表中:

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; 

查询1： 以下SELECT查询使用了上述Schoolboy_Marks表的Schoolboy_Last_Name列的MID函数：

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

这个SQL语句显示了每个学生的姓氏中第2个位置之后的4个字符。 nd

输出：

查询2： 下面的SELECT查询使用了上述Schoolboy_Marks表的Schoolboy_Last_Name列的MID函数：

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