SQL POSITION 函数

一、SQL POSITION 函数 语法

SQL语言中的 POSITION 字符串函数返回给定字符或子字符串在原始字符串中第一次出现的位置。如果原始字符串中省略了 Sub-string,则 POSITION 函数返回 0。

语法1:此语法使用带有 SQL 表的列名的 POSITION 函数:

SELECT POSITION(String IN Column_Name) AS Alias_Name FROM Table_Name; 

在语法中,我们必须指定要在其上查找字符位置的列的名称。

语法2:此语法使用带有字符串的 POSITION 函数:

SELECT POSITION(Sub-String IN Original_String) AS Alias_Name; 

语法3:此语法使用带有单个字符的 POSITION 函数:

SELECT POSITION(Character IN Original_String) AS Alias_Name;  

二、SQL POSITION 函数 示例

示例 1:以下 SELECT 查询显示字符 S 在原始字符串中的位置:

SELECT POSITION('S' IN 'SUSTAINABLE')AS POSITION_S; 

输出结果为:

POSITION_S
1

示例 2:以下 SELECT 查询显示 DELHI 单词在给定字符串中的位置:

SELECT POSITION( 'DELHI' IN 'NEW DELHI') AS POSITION_DELHI;  

输出结果为:

POSITION_DELHI
5

示例 3:以下 SELECT 查询返回“制造公司”字符串在原始字符串中的位置:

SELECT POSITION( 'Manufacturing Company' IN 'Honda is an indian Manufacturing company') AS Position_Manufacturing_company;  

输出结果为:

POSITION_Manufacturing_company
20

示例 4:此示例对结构化查询语言中的表使用 POSITION 函数。

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

CREATE TABLE table_name  
(  
First_Column_of_table Data Type (character_size of First Column),    
Second_Column_of_table Data Type (character_size of the Second column ),    
Third_Column_of_table Data Type (character_size of the Third column),    
...    
  
Last_Column_of_table Data Type (character_size of the Last column)  
);    

以下 CREATE 语句创建Student_Grade表:

CREATE TABLE Student_Grade  
(  
Roll_No INT PRIMARY KEY,    
First_Name VARCHAR (100),    
Last_Name VARCHAR (100),   
First_City Varchar(120),  
Second_City Varchar(120),  
New_City Varchar(120),  
Hindi_Marks INT,   
Maths_Marks INT,   
Grade Varchar (80)  
); 

下面的 INSERT 语句在Student_Grade表中插入学生的成绩和分数记录:

INSERT INTO Student_Grade (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade) VALUES (10, Aman, Sharma, Lucknow Chandigarh, Ghaziabad, 88, 95, A2);  
  
INSERT INTO Student_Grade   
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   
VALUES ( 02, Vishal, Sharma, Chandigarh, Ghaziabad, Delhi, 95, 82, A1 );  
  
INSERT INTO Student_Grade   
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   
VALUES (07, Raj, Gupta, Delhi, Ghaziabad, Lucknow, 91, 95, A1);  
  
INSERT INTO Student_Grade   
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)    
VALUES (04, Yash, Singhania, Ghaziabad, Delhi, Lucknow, 85, 82, A2);  
  
INSERT INTO Student_Grade   
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   
VALUES (11, Vinay, Roy, Delhi, Kanpur, Ghaziabad, 95, 97, A1);  
  
INSERT INTO Student_Grade   
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   
VALUES (16, Manoj, Gupta, Ghaziabad, Meerut, Chandigarh, 95, 90, B1);  
  
  
INSERT INTO Student_Grade   
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   
VALUES (19, Ram, Gupta, Lucknow, Ghaziabad, Chandigarh, 89, 95, A2);  

以下 SELECT 语句显示上述Student_Grade表的插入记录:

SELECT * FROM Student_Grade;   

输出结果为:

Roll_No First_Name Last_Name First_City Second_City New_City Hindi_Marks Maths_Marks Grade
10 Aman Sharma Lucknow Chandigarh Ghaziabad 88 95 A2
02 Vishal Sharma Chandigarh Ghaziabad Ghaziabad 95 82 A1
07 Raj Gupta Delhi Ghaziabad Lucknow 91 95 A1
04 Yash Singhania Ghaziabad Delhi Lucknow 85 82 A2
11 Vinay Roy Delhi Kanpur Ghaziabad 95 97 A1
16 Manoj Gupta Ghaziabad Meerut Chandigarh 95 90 B1
19 Ram Gupta Lucknow Ghaziabad Chandigarh 89 95 A2

查询 1:以下 SELECT 查询将 POSITION 函数与上述 Student_Grade 表的 First_Name 列一起使用:

SELECT First_Name, POSITION('a' IN First_Name) AS POSITION_a FROM Student_Grade;  

此 SQL 语句显示每个学生名字中“a”字符的位置。

输出结果为:

First_Name POSITION_a
Aman 1
Vishal 5
Raj 2
Yash 2
Vinay 4
Manoj 2
Ram 2

查询 2:以下 SELECT 查询将 POSITION 函数与上述 Student_Grade 表的 Last_Name 列一起使用:

SELECT Last_Name, POSITION('S' IN Last_Name) AS POSITION_S FROM Student_Grade;  

此 SQL 语句显示“S”字符在每个学生的姓中的位置。

输出结果为:

Last_Name POSITION_S
Sharma 1
Sharma 1
Gupta 0
Singhania 1
Roy 0
Gupta 0
Gupta 0

查询 3:以下 SELECT 查询将 POSITION 函数与上述 Student_Grade 表的 First_City 列一起使用:

SELECT First_City, POSITION('ow' IN First_City) AS POSITION_ow FROM Student_Grade;  

该 SQL 语句显示了“ow”字符在每个学生的第一个城市中的位置。

输出结果为:

First_City POSITION_ow
Lucknow 6
Chandigarh 0
Delhi 0
Ghaziabad 0
Delhi 0
Ghaziabad 0
Lucknow 6

查询 4:以下 SELECT 查询对上述 Student_Grade 表的 Second_City 和 New_City 列使用 POSITION 函数:

SELECT Second_City, POSITION(z IN Second_City), New_City, POSITION(a IN New_City) FROM Student_Grade; 

输出结果为:

Second_City POSITION(z IN Second_City) New_City POSITION(a IN New_City)
Chandigarh 0 Ghaziabad 3
Ghaziabad 4 Ghaziabad 3
Ghaziabad 4 Lucknow 0
Delhi 0 Lucknow 0
Kanpur 0 Ghaziabad 3
Meerut 0 Chandigarh 3
Ghaziabad 4 Chandigarh 3

热门文章

优秀文章