Ova funkcija niza Structured Query Language zamjenjuje sva pojavljivanja tih znakova u izvornom nizu danim novim znakom ili podnizom. Ova funkcija također zamjenjuje vrijednost stupca novom vrijednošću.

python // operator

Sintaksa funkcije niza REPLACE

Sintaksa 1: Ova sintaksa koristi funkciju REPLACE s nazivom stupca SQL tablice:

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

U sintaksi moramo navesti naziv stupca čije vrijednosti želimo zamijeniti.

Sintaksa 2: Ova sintaksa koristi funkciju REPLACE s nizom:

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

Sintaksa 3: Ova sintaksa koristi funkciju REPLACE s pojedinačnim znakom:

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

Primjeri funkcije REPLACE String

Primjer 1: Sljedeći SELECT upit zamjenjuje znak 'R' sa 'P' u originalnom nizu:

 SELECT REPLACE( 'JAVATROINT', 'R', 'P' ) AS Website_Name; 

Izlaz:

Primjer 2: Sljedeći SELECT upit zamjenjuje sva pojavljivanja znaka 'S' novim znakom 'T' u izvornom nizu:

 SELECT REPLACE( 'JAVASPOINS', 'S', 'T') AS Website_Name; 

Izlaz:

Primjer 3: Sljedeći SELECT upit zamjenjuje podniz 'Tutorials' novom riječi 'Articles' u danom originalnom nizu:

 SELECT REPLACE( 'JavaTpoint provides various Tutorials.', 'Tutorials', 'Articles') AS JavaTpoint_Sentence; 

Izlaz:

Primjer 4: Sljedeći SELECT upit zamjenjuje simbol iz izvornog niza novim simbolom:

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

Izlaz:

Primjer 5: Sljedeći SELECT upit zamjenjuje godinu u izvornom nizu:

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

Izlaz:

 2022JavaTpoint2022 

Primjer 6: Ovaj primjer koristi funkciju REPLACE s tablicom u jeziku strukturiranih upita.

U ovom primjeru moramo kreirati novu SQL tablicu kroz koju ćemo izvršavati REPLACE() funkciju na stupcima. Sintaksa za stvaranje nove tablice u SQL bazi podataka je sljedeća:

kako pretvoriti string u int java

 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) ); 

Sljedeća naredba CREATE stvara Ocjene_učenika stol:

 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 ); 

Donji INSERT upiti umeću zapise fakultetskih fakulteta u Ocjene_učenika stol:

 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); 

Sljedeća izjava SELECT prikazuje umetnute zapise od gore navedenog Ocjene_učenika stol:

 SELECT * FROM Student_Marks; 

Upit 1: Sljedeći SELECT upit koristi funkciju REPLACE sa stupcem Student_Last_Name gornje tablice Student_Marks:

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

Ova SQL izjava zamjenjuje sva pojavljivanja 'a' sa 'r' u prezimenu svakog učenika:

Izlaz:

Upit 2: Sljedeći SELECT upit koristi funkciju REPLACE sa stupcima Student_City i Student_State onih studenata čiji je Student_Id veći od 4002 u gornjoj tablici Student_Marks:

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

Ova SQL izjava zamjenjuje grad Chandigarh i državu Punjab onih učenika čiji je Student_ID veći od 4002.

Izlaz: