SQL INSTR函数

这个结构化查询语言的字符串函数展示了原始字符串中指定子字符串或模式的第一个位置。

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

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

SELECT INSTR(Original String, character);

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

INSTR字符串函数的示例

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

SELECT INSTR( 'JAVATPOINT',' 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查询显示了”印度首都”字符串在原始字符串中的位置：

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('2021JavaTpoint2021', '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; 

查询1: 以下SELECT查询使用了上述Teacher_Info表的Teacher_Address列的INSTR函数：

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

这个SQL SELECT语句显示了每个教师的地址中’Vihar’字符串的位置。

输出结果：

查询2： 以下SELECT查询使用了Teacher_Info表的Teacher_First_Name和Teacher_Last_Name列中的INSTR函数：

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的位置。

输出：