SQL LENGTH 函数

一、SQL LENGTH 函数 语法

SQL语言的 LENGTH 字符串函数返回给定字符串或单词的字符数。

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

SELECT LENGTH(Column_Name) AS Alias_Name FROM Table_Name;  

在语法中，我们必须指定要在其上执行 LENGTH 字符串函数以查找每个值的字符数的列的名称。

语法2：此语法使用带有字符串的 LENGTH 函数：

SELECT LENGTH(Original_String); 

二、SQL LENGTH 函数 示例

示例 1：以下 SELECT 查询显示给定 JAVATPOINT 字的字符总数：

SELECT LENGTH( ' YIIDIAN') AS LENGTH_word;  

输出结果为：

LENGTH_word
8

示例 2：以下 SELECT 查询显示给定字符串的字符总数：

SELECT LENGTH( 'YIIDIAN is a good website') AS LENGTH_string;  

输出结果为：

LENGTH_string
25

示例 3：以下 SELECT 查询显示给定句子的长度：

SELECT LENGTH( 'NEW DELHI IS THE CAPITAL OF INDIA') AS LENGTH_Sentence;  

输出结果为：

LENGTH_Sentence
33

示例 4：以下 SELECT 查询显示给定字符串的长度：

SELECT LENGTH( ' ' ) AS LENGTH_space;  

输出结果为：

LENGTH_space
1

示例 5：以下 SELECT 查询显示 NULL 字的长度：

SELECT LENGTH( NULL ) AS Length;  

输出结果为：

Length
NULL

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

在此示例中，我们将创建一个新的 SQL 表，我们希望在该表上执行 LENGTH 函数。

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

CREATE TABLE Employee_Grade  
(  
Employee_ID INT PRIMARY KEY,    
First_Name VARCHAR (100),    
Last_Name VARCHAR (100),   
First_City Varchar(120),  
Second_City Varchar(120),  
New_City Varchar(120),  
Attendance_Remarks INT,   
Work_Remarks INT,   
Grade Varchar (80)  
);  

下面的 INSERT 语句在Employee_Grade表中插入员工的成绩和备注记录：

INSERT INTO Employee_Grade (Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade) VALUES (10, Ramesh, Sharma, Lucknow Aurangabad, Ghaziabad, 88, 95, A2);  
  
INSERT INTO Employee_Grade   
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade)   
VALUES ( 02, Yadu, Sharma, Aurangabad, Ghaziabad, Noida, 95, 82, A1 );  
  
INSERT INTO Employee_Grade   
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade)   
VALUES (07, Vijay, Ramna, Noida, Ghaziabad, Lucknow, 91, 95, A1);  
  
INSERT INTO Employee_Grade   
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade)    
VALUES (04, Bhanu, Rangopalr, Ghaziabad, Noida, Lucknow, 85, 82, A2);  
  
INSERT INTO Employee_Grade   
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade)   
VALUES (11, Harry, Roy, Noida, Kanpur, Ghaziabad, 95, 97, A1);  
  
INSERT INTO Employee_Grade   
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade)   
VALUES (16, Akash, Ramna, Ghaziabad, Meerut, Aurangabad, 95, 90, B1);  
  
  
INSERT INTO Employee_Grade   
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade)   
VALUES (19, Ram, Ramna, Lucknow, Ghaziabad, Aurangabad, 89, 95, A2);  

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

SELECT * FROM Employee_Grade;   

输出结果为：

Employee_ID	First_Name	Last_Name	First_City	Second_City	New_City	Attendance_Remarks	Work_Remarks	Grade
10	Ramesh	Sharma	Lucknow	Aurangabad	Ghaziabad	88	95	A2
02	Yadu	Sharma	Aurangabad	Ghaziabad	Ghaziabad	95	82	A1
07	Vijay	Ramna	Noida	Ghaziabad	Lucknow	91	95	A1
04	Bhanu	Rangopalr	Ghaziabad	Noida	Lucknow	85	82	A2
11	Harry	Roy	Noida	Kanpur	Ghaziabad	95	97	A1
16	Akash	Ramna	Ghaziabad	Meerut	Aurangabad	95	90	B1
19	Ram	Ramna	Lucknow	Ghaziabad	Aurangabad	89	95	A2

查询 1：以下 SELECT 查询将 LENGTH 函数与上述 Employee_Grade 表的 First_Name 列一起使用：

SELECT First_Name, LENGTH(First_Name) AS LENGTH_FirstName FROM Employee_Grade;  

该声明显示了在该行业工作的每个员工的名字的长度。

First_Name	LENGTH_FirstName
Ramesh	6
Yadu	4
Vijay	5
Bhanu	5
Harry	5
Akash	5
Ram	3

查询 2：以下 SELECT 查询将 LENGTH 函数与上述 Employee_Grade 表的 Last_Name 列一起使用：

SELECT Last_Name, LENGTH(Last_Name) AS LENGTH_LastName FROM Employee_Grade;  

此语句显示每个员工的姓氏长度。

Last_Name	LENGTH_LastName
Sharma	6
Sharma	6
Ramna	5
Rangopalr	9
Roy	3
Ramna	5
Ramna	5

查询 3：以下 SELECT 查询对上述 Employee_Grade 表的 First_City 和 New_City 列使用 LENGTH 函数：

SELECT First_City, LENGTH(First_City) AS LENGTH_FirstCity, New_City, LENGTH(New_City) AS LENGTH_NewCity FROM Employee_Grade; 

此 SQL 语句显示每个员工的第一个和新城市的长度。

输出结果为：

First_City	LENGTH_FirstCity	New_City	LENGTH_NewCity
Lucknow	7	Ghaziabad	9
Aurangabad	10	Ghaziabad	9
Noida	5	Lucknow	7
Ghaziabad	9	Lucknow	7
Noida	5	Ghaziabad	9
Ghaziabad	9	Aurangabad	10
Lucknow	7	Aurangabad	10