SQL LOCATE 函数

一、SQL LOCATE 函数 语法

SQL语言的 LOCATE 函数 返回搜索字符串在原始字符串中的索引值。如果在原始字符串中没有找到搜索到的字符串，则函数返回 0。

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

SELECT LOCATE ( substring , Column_Name, [start_position]) AS Alias_Name FROM Table_Name; 

在语法中，我们必须指定要在其上运行 LOCATE 函数的列的名称。在函数 start_position 参数是可选的。

语法2：此语法使用带有原始字符串的 LOCATE 函数：

SELECT LOCATE (character or substring, Original string, [start_position]);  

二、SQL LOCATE 函数 示例

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

SELECT LOCATE('D', 'YIIDIAN', 1) AS LOCATE_D_Position; 

输出结果为：

LOCATE_D_Position

4

示例 2：以下 SELECT 查询使用 LOCATE 字符串函数显示原始字符串中第二个索引值的子字符串的位置：

SELECT LOCATE('.com12', 'Second.com12 Example ', 2) AS LOCATE_.com12_Position;  

输出结果为：

LOCATE_.com12_Position

6

示例 3：此示例将 LOCATE 函数与 SQL 表一起使用

要了解带有表的 LOCATE 函数，我们必须首先使用 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 查询将 LOCATE 函数与上述 Teacher_Info 表的 Teacher_Address 列一起使用：

SELECT Teacher_Address, LOCATE('Vihar', Teacher_Address, 3) AS LOCATE_Vihar_Position FROM Teacher_Info;  

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

输出结果为：

Teacher_Address	LOCATE_Vihar_Position
Aman Vihar	4
Nirman Vihar	6
Sector 128	0
Vivek Vihar	5
Sarvodya Calony	0
Krishna Nagar	0

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

SELECT Teacher_First_Name, LOCATE('a', Teacher_First_Name, 1) AS LOCATE_a_Position, Teacher_Last_Name, LOCATE('h', Teacher_Last_Name, 1) AS LOCATE_h_Position, FROM Teacher_Info;

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

输出结果为：

Teacher_First_Name	LOCATE_a_Position	Teacher_Last_Name	LOCATE_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