SQL STRCMP 函数

一、SQL STRCMP 函数 语法

SQL 中的 STRCMP 字符串函数比较在 STRCMP 函数中作为参数传递的两个字符串。如果第一个字符串的长度等于第二个字符串的长度，则此函数返回 0。如果第一个字符串的长度大于第二个字符串的长度，则返回 1，否则返回 -1。

语法1：此语法将 STRCMP 函数与 SQL 表的两列一起使用：

SELECT STRCMP (Column_Name1, column_Name2) AS Alias_Name FROM Table_Name;  

在语法中，我们必须定义要比较其长度的那些列的名称。

语法2：此语法使用带有两个字符串的 STRCMP 函数：

SELECT STRCMP (String_1, String_2);  

二、SQL STRCMP 函数 示例

示例 1：以下 SELECT 查询比较 YIIDIAN 和EXAMPLES 字符串的长度：

SELECT STRCMP ('YIIDIAN', 'EXAMPLES');  

输出结果为：

1

示例 2：以下 SELECT 查询比较在 STRCMP 函数中传递的两个句子的长度：

SELECT STRCMP ('Website is good', 'yiidian is good');  

输出结果为：

-1

示例 3：以下 SELECT 查询比较两个城市的长度：

SELECT STRCMP (  'Delhi', 'Noida');  

输出结果为：

0

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

现在，我们创建新的 SQL 表，这有助于理解 STRCMP 字符串函数。

在 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 语句创建Worker_Info表：

CREATE TABLE Worker_Info  
(  
Worker_ID INT NOT NULL PRIMARY KEY,    
Worker_First_Name VARCHAR (100),    
Worker_Last_Name VARCHAR (100),    
Worker_Dept_Id INT NOT NULL,  
Worker_Address Varchar(120),  
Worker_City Varchar (80),  
Worker_Salary INT   
);  

下面的 INSERT 语句在Worker_Info表中插入行业工人的记录：

INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1001, Arush, Sharma, 4001, Aman Vihar, Delhi, 20000);  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1002, Bulbul, Roy, 4002, Nirman Vihar, Delhi, 38000 );  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1004, Saurabh, Sharma, 4001, Sector 128, Mumbai, 45000);  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1005, Shivani, Singhania, 4001, Vivek Vihar, Kolkata, 42000);  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1006, Avinash, Sharma, 4002, Sarvodya Calony, Delhi, 28000);  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary)VALUES (1007, Shyam, Besas, 4003, Krishna Nagar, Lucknow, 35000);  

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

SELECT * FROM Worker_Info;  

输出结果为：

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Address	Worker_City	Worker_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

以下 SELECT 查询将 STRCMP 函数与上述 Worker_Info 表的 Worker_First_Name 和 Worker_City 列一起使用：

SELECT Worker_First_Name, Worker_City, STRCMP (Worker_First_Name, Worker_City) AS Compare_Firstname_City FROM Worker_Info;  

此 SQL 查询比较每个工作人员的名字和城市。

输出结果为：

Worker_First_Name	Worker_City	Compare_Firstname_City
Arush	Delhi	0
Bulbul	Delhi	1
Saurabh	Mumbai	1
Shivani	Kolkata	0
Avinash	Delhi	1
Shyam	Lucknow	-1

以下 SELECT 查询将 STRCMP() 与上述 Worker_Info 表的 Worker_first_Name 和 Worker_Last_Name 列一起使用：

SELECT Worker_First_Name, Worker_Last_Name, STRCMP(Worker_First_Name, Worker_Last_Name) AS STRCMP_Firstname_Lastname FROM Worker_Info;

此 SQL 语句比较每个 Worker 的名字和姓氏。

输出结果为：

Worker_First_Name	Worker_Last_Name	STRCMP_Firstname_Lastname
Arush	Sharma	-1
Bulbul	Roy	1
Saurabh	Roy	1
Shivani	Singhania	-1
Avinash	Sharma	1
Shyam	Besas	0