SQL INSTR 函数

一、SQL INSTR 函数 语法

SQL语言的这个 INSTR 函数 显示指定子字符串或模式在原始字符串中的第一个位置。

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

SELECT INSTR(Column_Name1, Substring or Pattern) AS Alias_Name FROM Table_Name;  

在这种语法中，我们必须指定要对其执行 INSTR 函数的列的名称。

语法2：此语法使用带有字符集（字符串）的 INSTR 函数：

SELECT INSTR(Original String, Substring or Pattern);  

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

SELECT INSTR(Original String, character);  

在这种语法中，我们必须指定要在字符串中查找其位置的单个字符。

二、SQL INSTR 函数 示例

示例 1：以下 SELECT 查询查找 'P' 字符在 JAVATPOINT 字符串中的位置：

SELECT INSTR( 'YIIDIAN',' P ') AS INSTR_P_Position;  

输出结果为：

INSTR_P_Position

6

示例 2：以下 SELECT 查询使用 INSTR 字符串函数显示子字符串在原始字符串中的位置：

SELECT INSTR('Second.com12 Example ','.com12' ) AS INSTR_.com12_Position; 

输出结果为：

INSTR_.com12_Position

7

示例 3：以下 SELECT 查询显示 CAPITAL OF INDIA 字符串在原始字符串中的位置：

SELECT INSTR( 'NEW DELHI IS THE CAPITAL OF INDIA', 'CAPITAL OF INDIA') AS INSTR_Substring_Position;  

输出结果为：

INSTR_Substring_Position

18

示例 4：以下 SELECT 查询从指定字符串中修剪给定符号：

SELECT INSTR( '##@@98221545#@#', '@') AS INSTR_@_Position;

输出结果为：

INSTR_@_Position

3

示例 5：以下 SELECT 查询从指定字符串中修剪给定的一组数字：

SELECT INSTR('2021Yiidian2021', '2021') AS INSTR_2021_Position;  

输出结果为：

INSTR_2021_Position

1

示例 6：此示例将 INSTR 函数与 SQL 表一起使用

要理解带表的 INSTR 函数，我们必须首先使用 CREATE TABLE 语句创建 SQL 表。

在 SQL 数据库中创建新表的语法如下：

CREATE TABLE table_name  
(  
1st_Column Data Type (character_size of 1st Column),    
2nd_Column Data Type (character_size of the 2nd column ),    
3rd_Column Data Type (character_size of the 3rd column),    
...    
Nth_Column Data Type (character_size of the Nth column)  
);   

以下 CREATE 语句创建Teacher_Info表：

CREATE TABLE Teacher_Info  
(  
Teacher_ID INT NOT NULL PRIMARY KEY,    
Teacher_First_Name VARCHAR (100),    
Teacher_Last_Name VARCHAR (100),    
Teacher_Dept_Id INT NOT NULL,  
Teacher_Address Varchar(120),  
Teacher_City Varchar (80),  
Teacher_Salary INT   
);  

下面的 INSERT 语句将大学教师的记录插入到Teacher_Info表中：

INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1001, Arush, Sharma, 4001, Aman Vihar, Delhi, 20000);  
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1002, Bulbul, Roy, 4002, Nirman Vihar, Delhi, 38000 );  
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1004, Saurabh, Sharma, 4001, Sector 128, Mumbai, 45000);  
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1005, Shivani, Singhania, 4001, Vivek Vihar, Kolkata, 42000);  
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1006, Avinash, Sharma, 4002, Sarvodya Calony, Delhi, 28000);  
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary)VALUES (1007, Shyam, Besas, 4003, Krishna Nagar, Lucknow, 35000);  

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

SELECT * FROM Teacher_Info; 

输出结果为：

Teacher_Id	Teacher_First_Name	Teacher_Last_Name	Teacher_Dept_Id	Teacher_Address	Teacher_City	Teacher_Salary
1001	Arush	Sharma	4001	Aman Vihar	Delhi	20000
1002	Bulbul	Roy	4002	Nirman Vihar	Delhi	38000
1004	Saurabh	Roy	4001	Sector 128	Mumbai	45000
1005	Shivani	Singhania	4001	Vivek Vihar	Kolkata	42000
1006	Avinash	Sharma	4002	Sarvodya Calony	Delhi	28000
1007	Shyam	Besas	4003	Krishna Nagar	Lucknow	35000

查询 1：以下 SELECT 查询将 INSTR 函数与上述 Teacher_Info 表的 Teacher_Address 列一起使用：

SELECT Teacher_Address, INSTR(Teacher_Address, 'Vihar') AS INSTR_Vihar_Position FROM Teacher_Info;  

此 SQL SELECT 语句显示“Vihar”字符串在每个教师地址中的位置。 

输出结果为：

Teacher_Address	INSTR_Vihar_Position
Aman Vihar	6
Nirman Vihar	8
Sector 128	0
Vivek Vihar	7
Sarvodya Calony	0
Krishna Nagar	0

查询 2：以下 SELECT 查询将 INSTR 函数与 Teacher_Info 表的 Teacher_First_Name 和 Teacher_Last_Name 列一起使用：

SELECT Teacher_First_Name, INSTR(Teacher_First_Name, a) AS instr_a_Position, Teacher_Last_Name, INSTR(Teacher_Last_Name, h) AS instr_h_Position, FROM Teacher_Info; 

该 SQL 语句显示了每个教师名字中字符 a 和姓氏中字符 h 的位置。

输出结果为：

Teacher_First_Name	instr_a_Position	Teacher_Last_Name	instr_h_Position
Arush	1	Sharma	2
Bulbul	0	Roy	0
Saurabh	2	Roy	0
Shivani	5	Singhania	5
Avinash	1	Sharma	2
Shyam	4	Besas	0