SQL REPLACE 函数

一、SQL REPLACE 函数 语法

SQL语言的 REPLACE 函数 用给定的新字符或子字符串替换原始字符串中所有出现的这些字符。此函数还将列的值替换为新值。

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

SELECT REPLACE(Column_Name, Character/string_to_replace, new_String/character ) AS Alias_Name FROM Table_Name;  

在语法中,我们必须指定要替换其值的列的名称。

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

SELECT REPLACE(Original_String, String_to_Replace, New_String) AS Alias_Name; 

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

SELECT REPLACE(Original_String, character_to_Replace, New_Character) AS Alias_Name;  

二、SQL REPLACE 函数 示例

示例 1:以下 SELECT 查询将原始字符串中的字符“R”替换为“D”:

SELECT REPLACE(  'YIIRIAN', 'R', 'D' ) AS Website_Name;  

输出结果为:

Website_Name
YIIDIAN

示例 2:以下 SELECT 查询将所有出现的字符 'S' 替换为原始字符串中的新字符 'A':

SELECT REPLACE( 'YIIDISN', 'S', 'A') AS Website_Name;  

输出结果为:

Website_Name
YIIDIAN

示例 3:以下 SELECT 查询将子字符串 'Tutorials' 替换为给定原始字符串中的新单词 'Articles':

SELECT REPLACE(  'Yiidian provides various Tutorials.', 'Tutorials', 'Articles') AS Yiidian_Sentence; 

输出结果为:

Yiidian_Sentence
YIIDIAN provides various Articles.

示例 4:以下 SELECT 查询将原始字符串中的符号替换为新符号:

SELECT REPLACE(  '####98221545###', '#', '$') AS Replace_Symbol ;  

输出结果为:

$$$$98221545$$

示例 5:以下 SELECT 查询替换原始字符串中的年份:

SELECT REPLACE(  '2021Yiidian2021', '2021', '2022');  

输出结果为:

2022Yiidian2022   

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

在这个例子中,我们必须创建一个新的 SQL 表,通过它我们将对列执行 REPLACE() 函数。在 SQL 数据库中创建新表的语法如下:

CREATE TABLE table_name  
(  
First_Column_of_table Data Type (character_size of 1st Column),    
Second_Column_of_table Data Type (character_size of the 2nd column ),    
Third_Column_of_table Data Type (character_size of the 3rd column),    
...    
  
Last_Column_of_table Data Type (character_size of the Nth column)  
);    

以下 CREATE 语句创建Student_Marks表:

CREATE TABLE Student_Marks  
(  
Student_ID INT NOT NULL PRIMARY KEY,    
Student_First_Name VARCHAR (100),    
Student_Middle_Name VARCHAR (100),    
Student_Last_Name VARCHAR (100),   
Student_Class INT NOT NULL,  
Student_City Varchar(120),  
Student_State Varchar (80),  
Student_Marks INT   
);  

下面的 INSERT 语句在Student_Marks表中插入大学学院的记录:

INSERT INTO Student_Marks (Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) VALUES (4001, Aman, Roy, Sharma, 4, Chandigarh, Punjab, 88);  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES ( 4002, Vishal, Gurr, Sharma, 8, Murthal, Haryana, 95 );  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES (4007, Raj, singhania, Gupta, 6, Ghaziabad, Uttar Pradesh, 91);  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)    
VALUES (4004, Yash, Chopra, Singhania, 9, Jaipur, Rajasthan, 85);  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES (4011, Vinay, Sharma, Roy, 8, Chandigarh, Punjab, 94);  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES (4006, Manoj, singhania, Gupta, 5, Ghaziabad, Uttar Pradesh, 83);  
  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES (4010, Ram, Raheem, Gupta, 9, Lucknow, Uttar Pradesh, 89);  

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

SELECT * FROM Student_Marks;   

输出结果为:

Student_Id Student_First_Name Student_Middle_Name Student_Last_Name Student_Class Student_City Student_State Student_Marks
4001 Aman Roy Sharma 4 Chandigarh Punjab 88
4002 Vishal Gurr Sharma 8 Murthal Haryana 95
4007 Raj singhania Gupta 6 Ghaziabad Uttar Pradesh 91
4004 Yash Chopra Singhania 9 Jaipur Rajasthan 85
4011 Vinay Sharma Roy 8 Chandigarh Punjab 94
4006 Manoj Singhania Gupta 5 Ghaziabad Uttar Pradesh 83
4010 Ram Raheem Gupta 9 Lucknow Uttar Pradesh 89

查询 1:以下 SELECT 查询对上述 Student_Marks 表的 Student_Last_Name 列使用 REPLACE 函数:

SELECT Student_Last_Name, REPLACE(Student_Last_Name, 'a', 'r') AS REPLACE_a_r FROM Student_Marks;  

此 SQL 语句将每个学生的姓氏中所有出现的 'a' 替换为 'r':

输出结果为:

Student_Last_Name Replace_a_r
Sharma Shrrmr
Sharma Shrrmr
Gupta Guptr
Singhania Singhrnir
Roy Roy
Gupta Guptr
Gupta Guptr

查询 2:以下 SELECT 查询对上述 Student_Marks 表中 Student_Id 大于 4002 的学生的 Student_City 和 Student_State 列使用 REPLACE 函数:

SELECT Student_Id, REPLACE(Student_City, 'Chandigarh', 'Munnar'), REPLACE(Student_State,  'Punjab', 'Kerala ) FROM Student_Marks WHERE Student_Id >4002;

此 SQL 语句替换 Student_ID 大于 4002 的学生的Chandigarh市和Punjab市。

Student_Id REPLACE(Student_City, 'Chandigarh', 'Munnar') REPLACE(Student_State, 'Punjab', 'Kerala )
4007 Ghaziabad Uttar Pradesh
4004 Jaipur Rajasthan
4011 Munnar Kerala
4006 Ghaziabad Uttar Pradesh
4010 Lucknow Uttar Pradesh

 

热门文章

优秀文章