SQL LOCATE函数

这个结构化查询语言中的字符串函数返回原始字符串中搜索字符串的索引值。如果在原始字符串中找不到搜索字符串，则该函数返回0。

LOCATE字符串函数的语法

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

LOCATE字符串函数示例

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

SELECT LOCATE('P', 'JAVATPOINT', 1) AS LOCATE_P_Position;

输出:

LOCATE_P_Position

6

示例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;

查询1： 下面的SELECT查询使用以上Teacher_Info表的Teacher_Address列的LOCATE函数：

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

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

输出：

查询2： 下面的SELECT查询使用了Teacher_Info表中的Teacher_First_Name和Teacher_Last_Name列与LOCATE函数：

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

输出：