SQL ASCII 函数
一、SQL ASCII 函数 语法
SQL语言的 ASCII 函数显示字符串第一个字符的 ascii 值。我们还可以将 ASCII 函数与 SQL 表的字符串字段一起使用。
语法1:此语法使用带有 SQL 表列名的 ASCII 函数:
SELECT ASCII (Column_Name) AS Alias_Name FROM Table_Name;
在语法中,我们必须指定要在其上使用 ASCII 字符串函数的列名。
语法2:此语法使用带有字符集(字符串)的 ASCII 函数:
SELECT ASCII (String);
语法3:此语法使用带有单个字符的 ASCII 函数:
SELECT ASCII (character);
二、SQL ASCII 函数 示例
示例 1:以下 SELECT 查询显示给定字符串的 ASCII 值:
SELECT ASCII (YIIDIAN) AS ASCII_Y;
输出结果为:
ASCII_J |
89 |
示例 2:以下 SELECT 查询显示给定字符串的第一个字符的 ASCII 值:
SELECT ASCII (New Delhi IS the Capital OF INDIA) AS ASCII_N;
输出结果为:
ASCII_N |
78 |
示例 3:以下 SELECT 查询显示 'R' 字符的 ASCII 值:
SELECT ASCII (R) AS ASCII_R;
输出结果为:
ASCII_R |
82 |
示例 4:此示例将 ASCII 函数与 SQL 表一起使用
在这个例子中,我们将创建一个新的 SQL 表,我们必须在该表上执行 ASCII 函数来查找第一个字符的 ASCII 值。
在 SQL 数据库中创建新表的语法如下:
CREATE TABLE table_name
(
First_Column_of_SQLtable Data Type (character_size of First Column),
Second_Column_of_SQLtable Data Type (character_size of the Second column ),
Third_Column_of_SQLtable 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 查询将 ASCII 函数与上述 Student_Grade 表的 Last_Name 列一起使用:
SELECT Last_Name, ASCII (Last_Name) AS ASCII _LastName FROM Student_Grade;
此 SQL 语句显示表中姓氏的第一个字符的 ASCII 值。
输出结果为:
Last_Name | ASCII _LastName |
---|---|
SHARMA | 83 |
SHARMA | 83 |
GUPTA | 71 |
SINGHANIA | 83 |
ROY | 82 |
GUPTA | 71 |
GUPTA | 71 |
查询 2:以下 SELECT 查询将 ASCII 函数与上述 Student_Grade 表中 Roll_No 大于 2 的学生的 First_City、Second_City 和 New_City 列一起使用:
SELECT Roll_No, ASCII (First_City), ASCII (Second_City), ASCII (New_City) FROM Student_Grade WHERE Roll_No > 2;
输出结果为:
Roll_No | ASCII(First_City) | ASCII(Second_City) | ASCII(New_City) |
---|---|---|---|
07 | 68 | 71 | 76 |
04 | 71 | 68 | 76 |
11 | 68 | 75 | 71 |
16 | 71 | 77 | 67 |
19 | 76 | 71 | 67 |
热门文章
优秀文章