SQL REPLACE() Function
The SQL REPLACE() function is used to replace one or more characters (sequence of characters or string) from a string or expression.
The SQL REPLACE() function will replace all available matched strings.
The SQL REPLACE() function is supports or work with character and numeric based columns.
It can be used in any valid SQL SELECT statement as well in SQL where clause.
Related Links
SQL REPLACE() Syntax
The below syntax is used to replace strings from a given input string or expression.
SELECT REPLACE(string1, find_character_or_string, replace_character_or_string);
The below syntax is used to replace strings from a in a specific column value.
SELECT REPLACE(column_name1, find_character_or_string, replace_character_or_string) FROM table_name;
Parameter Name | Description |
---|---|
string or column_name | Required. The string to replace a sequence of characters or a string with another set of characters or string. |
find_character_or_string | Required. The sequence of characters or string that will be searched for to replace in string1 or column_name1. |
replace_character_or_string | Required. The replacement character or string. All occurrences of "find_character_or_string" found within string1 or column_name1 are replaced with "replace_character_or_string". |
SQL REPLACE() Example - Using Expression Or Formula
The following SQL SELECT statement replace sequence of characters or a string within a string.
SELECT REPLACE('Sql Tutorial.', '.', '!') AS 'Replaced String';
The result of above query is:
Replaced String |
---|
Sql Tutorial! |
SQL REPLACE() Function More Example
Input Value | Result |
---|---|
REPLACE('Database', 'a', '@') | D@t@b@se |
REPLACE('Sql Tutorial', 'Sql', 'Pl Sql') | Pl Sql Tutorial |
REPLACE('Sql Query or Command', 'or', '/') | Sql Query / Command |
REPLACE('Simmanchith.com', 'c', 'C') | SimmanChith.Com |
Sample Database Table - Employee
ID | EmpName | Designation | Dept | JoinYear | Salary |
---|---|---|---|---|---|
1 | Siva Kumar | SQL Mining | MySQL | 2013 | 9140 |
2 | Hanumanthan | Cloud Database | MS Access | 2012 | 12500 |
3 | Sakunthala | Project Manager | PHP | 2015 | 12500 |
4 | Geetha | SQL Security | Java | 2014 | 17330.8 |
5 | Bala Murugan | Database Security | PHP | 2014 | 19640 |
6 | Padmavathi | Developer | PHP | 2012 | 5360 |
SQL REPLACE() Example
The following SQL statement replace the "EmpName" and "Dept" column from the "Employee" table:
SELECT ID,
EmpName, REPLACE(EmpName, 'a', '@') As 'Replaced - EmpName',
Dept, REPLACE(Dept, 'PHP', 'Personal Home Page') As 'Replaced - Dept'
FROM Employee;
The result of above query is:
ID | EmpName | Replaced - EmpName | Dept | Replaced - Dept |
---|---|---|---|---|
1 | Siva Kumar | Siv@ Kum@r | MySQL | MySQL |
2 | Hanumanthan | H@num@nth@n | MS Access | MS Access |
3 | Sakunthala | S@kunth@l@ | PHP | Personal Home Page |
4 | Geetha | Geeth@ | Java | Java |
5 | Bala Murugan | B@l@ Murug@n | PHP | Personal Home Page |
6 | Padmavathi | P@dm@v@thi | PHP | Personal Home Page |
Related Links
SQL Replace Multiple Characters
The REPLACE() characteristic allows us to update a string with any other string. in case you need to update a listing of characters with any other listing of characters the use of TRANSLATE() characteristic.
Returns the string supplied as a primary argument after a few characters designated withinside the 2nd argument are translated right into a vacation spot set of characters designated withinside the 0.33 argument.
Example 1: I think this is one of those times that really shouts out for an example.
SELECT TRANSLATE('Fred [10 points]', '[]', '()');
Result:
Basically, it’s as though we’ve provided a list of values to replace another list of values. But there’s no need to separate each list item with a separator.
Example 2: Equal Number of Characters
The second and third arguments must contain an equal number of characters.
In other words, you can’t do this:
SELECT TRANSLATE('Fred [10 points]', '[]', '(');
Result:
The second and third arguments of the built-in TRANSLATE function must contain an equal number of characters. In this case the second argument contains two characters but the third argument contains only one, so we get an error.
This happens because SQL Server needs to know which character to replace the second argument's second character with. It goes through each character, one at a time, replacing it with the corresponding character from the third argument. If not, it has no choice but to throw an error.
Example 3: Here’s an example that illustrates the difference between TRANSLATE() and REPLACE().
SELECT REPLACE('[] [hey]', '[]', '()') AS REPLACE,
TRANSLATE('[] [hey]', '[]', '()') AS TRANSLATE;
Result:
REPLACE | TRANSLATE |
---|---|
() [hey] | () (hey) |
The REPLACE() characteristic leaves [hey] precisely because it is, due to the fact that entire string wasn`t furnished withinside the 2nd argument. This characteristic best reveals a fit if the entire string is present.
The TRANSLATE() characteristic alternatively replaces [hey] with (hey) as it replaces every man or woman one through one. It`s now no longer seeking out an entire string to replace, it`s best seeking out every person man or woman individually.
SQL Replace Multiple Patterns
Example 1: The following example uses the SQL replace function to replace multiple patterns of the expression 3*[4+5]/{6-8}.
SELECT REPLACE(REPLACE(REPLACE(
REPLACE('3*[4+5]/{6-8}', '[', '('), ']', ')'), '{', '('), '}', ')');
We can see that the REPLACE characteristic is nested and it's far referred to as more than one instances to update the corresponding string as in line with the described positional values inside the SQL REPLACE characteristic.
In the aforementioned example, we will use the TRANSLATE, a brand new SQL Server 2017 characteristic. It`s an amazing substitute string characteristic for the SQL REPLACE characteristic.
Example 2: The following query replaces the pattern A, C and D with the values 5, 9 and 4 and generates a new column named GRPCODE:
DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp
(name NVARCHAR(50),
GRP NVARCHAR(100)
);
INSERT INTO #temp
VALUES
('Prashanth',
'AB'
),
('Kiki',
'ABC'
),
('Steven',
'ABCD'
);
The underneath SQL REPLACE feature undergoes an execution of three iterations to get the favored result. The first, enter pattern `A` is evaluated and if located, five are changed. The second, B is evaluated. If located the numeric cost nine is changed. Finally, D is changed via way of means of 4.
SELECT Name,
GRP,
REPLACE (REPLACE (REPLACE(GRP, 'A', '5'), 'C', 9), 'D', 4) GRPCODE
FROM #temp;
Replace multiple patterns in given string:
Here is an example to update using the SQL REPLACE function. In this case, GRP column with the GRP CODE, run the following SQL.
UPDATE #temp
SET
GRP = replace(replace(REPLACE(GRP, 'A', '5'), 'C', 9), 'D', 4);
Now, let’s take a look at the data,
SELECT * FROM #temp;
SQL Replace Part of String
Example 1: Replace part of a string with another string.
To replace all occurrences of a substring within a string with a new substring, you use the REPLACE() function as follows:
REPLACE(input_string, substring, new_substring); ...
SELECT REPLACE( 'It is a good tea at the famous tea store.', '
Example 2: Our database has a table named investor with data in the following columns: id, company, and phone.
id | company | phone |
---|---|---|
1 | Big Market | 123–300-400 |
3 | The Sunny Restaurant | 123–222-456 |
4 | My Bank | 123-345-400 |
We’d like to change the phone number format for each company by replacing the hyphen character with a space.
SELECT REPLACE( phone, '-', ' ' ) as new_phone
FROM investor;
The query returns a list of phone numbers with spaces instead of hyphens as separators:
new_phone |
---|
123 300 400 |
123 222 456 |
123 345 400 |
Discussion:
If you'd want to update a substring with every other string, without a doubt use the REPLACE feature. This feature takes 3 arguments:
- The string to change (which in our case became a column).
- The substring to update.
- The string with which to update the required substring.
Example 3: In this example, we replace the adjective 'Big' in the company column with 'Small'.
SELECT REPLACE( company, 'Big', 'Small' ) as new_company
FROM investor
WHERE id = 1;
This query display the new name of the company for the investor with id = 1.
new_company |
---|
Small Market |
SQL Replace Case Sensitive
REPLACE() function is case-sensitive.
Example 1: The following query successfully replaces the substring since “STATES” occurs in “UNITED STATES” and is replaced by “KINGDOM”.
SELECT REPLACE('UNITED STATES', 'STATES', 'KINGDOM');
On the other hand, the following query is unable to successfully replace the substring since “states” does not occur in “UNITED STATES”.
SELECT REPLACE('UNITED STATES', 'states', 'KINGDOM');
Example 2:
SELECT REPLACE( policy_id, 'v', '5' ) AS new_policy_id,
last_name,
first_name
FROM life_insurance;
Result:
policy_id | last_name | first_name |
---|---|---|
5-01 | Anstruther - Gough | Gary |
5-23 | Elliot - Murray - Stewart | Mary |
3A-5 | Smith - Dorrie | Alex |
This query replaces both `v` and `V` with `5` in the policy_id column. By default, SQL Server's REPLACE function is case-insensitive, but it really depends on your database server settings.
Example 3: You can specify whether this function is case-sensitive by using the COLLATE clause:
SELECT REPLACE( policy_id COLLATE SQL_Latin1_General_CP1_CS_AS, 'v', '5' )
AS new_policy_id,
last_name,
first_name
FROM life_insurance;
Output:
policy_id | last_name | first_name |
---|---|---|
5-01 | Anstruther - Gough | Gary |
5-23 | Elliot - Murray - Stewart | Mary |
3A-5 | Smith - Dorrie | Alex |
In this query, `V` is not replaced because the REPLACE function is now case-sensitive (i.e. `v` is not the same as `V`). In the COLLATE clause, _CS indicates case-sensitive; if you change it to _CI, the function will be case insensitive.
Additional advice: What is a rating?
In SQL Server, it is a set of rules that define how values should be compared and sorted, and how accents should be handled. It may be handled differently in different database systems. COLLATION is an SQL clause that defines a collation using various options such as _CI, _CS, and _AS, and the name of an encoder (such as SQL_Latin1_General).
SQL Replace First Occurence
APPROACH 1: First approach with CROSS APPLY and SUBSTRING:
Example: Have you ever considered how REPLACE works? It simply replaces all instances of a term in a sentence.
CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )
INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')
Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'
select id,string,
Replace(string,@searchstring,@replacestring)
from #StringTable
cross apply (select (charindex(@searchstring, string))) as Search1(Pos)
Drop table #StringTable
In the above example, there are two occurrences of the word “was” for the first record. And the REPLACE function results in replacing all occurrences.
APPROACH 2:
Example: Suppose, if you want to only replace the first occurrence, How do you do?
CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )
INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')
Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'
select id,string,
case when Search1.Pos != 0 then
substring(string, 0,Search1.Pos )+ @Replacestring +
substring(string, Search1.Pos+LEN(@searchstring), LEN(string) )
else string end ChangedText
from #StringTable
cross apply (select (charindex(@searchstring, string))) as Search1(Pos)
Drop table #StringTable
APPROACH 3: STUFF and CHARINDEX
Example: use a combination of STUFF and CHARINDEX to achieve what you want:
SELECT STUFF(col, CHARINDEX('substring', col),
LEN('substring'), 'replacement')
FROM #temp
CHARINDEX('substring', col) -will return the index of the first occurrence of 'substring' in the column. STUFF then replaces this occurrence with 'replacement'.
APPROACH 4:
You can achieve this with the help of CONCAT() along with REPLACE() function. To find the first occurrences you need to use INSTR() function.
The syntax is as follows:
UPDATE yourTableName
SET UserPost = CONCAT(REPLACE(LEFT(yourColumnName,
INSTR(yourColumnName, 'k')), 'k', 'i'),
SUBSTRING(yourColumnName, INSTR(yourColumnName, 'k') + 1));
Example 1: To understand the above syntax, let us create a table. The query to create a table is as follows:
mysql> create table UserInformation
-> (
-> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> UserName varchar(10),
-> UserPost text
-> );
Now you can insert some records in the table using insert command. The query is as follows:
mysql> insert into UserInformation(UserName,UserPost) values('Larry','Thks is a MySQL query');
mysql> insert into UserInformation(UserName,UserPost) values('Mike','Thks is not a java program');
mysql> insert into UserInformation(UserName,UserPost) values('Sam','Thks is a SQL syntax');
Display all records from the table using a select statement. The query is as follows:
mysql> select *from UserInformation;
Output:
UserId | UserName | UserPost |
---|---|---|
1 | Larry | Thks is a MySQL query |
2 | Mike | Thks is not a java program |
3 | Sam | Thks is a SQL syntax |
Example 2: Here is the query to search/replace but only the first time a value appears in a record. Here, the first occurrence of ‘k’ is replaced with ‘i’:
mysql> update UserInformation
-> set UserPost=CONCAT(REPLACE(LEFT(UserPost, INSTR(UserPost, 'k')), 'k', 'i'),
-> SUBSTRING(UserPost, INSTR(UserPost, 'k') + 1));
Display all records from the table once again. The query is as follows:
mysql> select *from UserInformation;
The following is the output displaying the first occurrence of ‘k’ replaced with ‘I’:
UserId | UserName | UserPost |
---|---|---|
1 | Larry | Thks is a MySQL query |
2 | Mike | Thks is not a java program |
3 | Sam | Thks is a SQL syntax |
SQL Replace Special Characters
Any characters that aren't alphanumeric are considered special characters.
In fact, these functions allow you to specify the characters you want to eliminate.
Perhaps it's only the carriage return and new line characters that are causing the problem. Perhaps it's symbols like # and!.
Example 1: To check for the carriage return, use the CHR(13) function. To find the newline character, use CHR(10).
You can replace special characters using the Oracle REPLACE function.
For example, to replace a carriage return with a space:REPLACE(your_column, CHR(13), ' ')
To replace both carriage return and new line characters, you must use nested REPLACE functions.
REPLACE(REPLACE(your_column, CHR(13), ' '), CHR(10), ' ')
If you want to replace a lot of special characters, using many nested REPLACE functions can get messy and could have performance impacts.
Replacing ASCII Printable Characters
The American Standard System for Information Interchange (ASCII) is a standardised numeric code for encoding character data in a computer that is widely used. The ASCII numeric code for the backslash () character, for example, is 92. Similarly, SQL Server, which utilises ANSI — an upgraded version of ASCII – includes a built-in CHAR function for converting an ASCII numerical value back to its original character code (or symbol).
SELECT CHAR(92);
Example 1: The backslash character belongs to the ASCII Printable Characters group of ASCII characters, which refers to characters that are visible to the naked eye. Table 1 displays the top 5 ASCII Printable Characters.
NumericCode | Character | Description |
---|---|---|
33 | ! | Exclamation Mark |
35 | # | Number |
36 | $ | Dollar |
37 | % | Percent |
38 | & | Ampersand |
When it comes to addressing data quality issues in SQL Server, using the REPLACE function to clean up most of the ASCII Printable Characters is simple. Consider the case where the source data includes an email address for John Doe that has multiple invalid special characters, as shown in Script.
DECLARE @email VARCHAR(55) = 'johndoe@a!b#c.com$';
We could eliminate such characters by applying the REPLACE T-SQL function as shown in Script 3.
SELECT REPLACE(REPLACE(REPLACE(@email, '!', ''), '#', ''), '$', '');
SQL Replace Between 2 Characters
Much like changing a string after a positive character, we can not at once use the Replace() characteristic at once to update string among characters. But a essential method to reap this will be as follows.
- First, use the Charindex() characteristic to discover the placement of each the characters in a given expression.
- Then, use the ones role values withinside the Substring() characteristic to extract the substring among the ones characters.
- In the last, use the extracted substring in Replace() characteristic to update it with a brand new substring.
Now for demonstration, we're going to update the substring among `@` and first `.` in an e-mail deal with with a brand new substring. And for this, we're going to use the subsequent query.
SELECT [email], REPLACE([email],
SUBSTRING([email],
CHARINDEX('@',[email])+1,
CHARINDEX('.', [email])-CHARINDEX('@', [email])-1),
'gmail')
AS [Updated Email]
FROM [Sample]
The above query uses the Replace() function inside the SELECT statement. The Replace() function defined an email field as the expression to look up.
Then I used the Substring() function. The Substring() function used two Charindex() functions to extract the substring between '@' and '.'.
Finally, use the extracted substring and replace it with the "gmail" substring.
SQL Replace Multiple Characters
Example 1: The following example uses the SQL replace function to replace multiple patterns of the expression 3*[4+5]/{6-8}.
SELECT REPLACE(REPLACE(REPLACE(REPLACE('3*[4+5]/{6-8}',
'[', '('), ']', ')'), '{', '('), '}', ')');
We can see that the REPLACE function is nested and it is called multiple times to replace the corresponding string according to the positional values defined in the SQL REPLACE function.
Example 2: The following query replaces patterns A, C, and D with the values 5, 9, and 4 and creates a new column named GRPCODE:
DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp
(name NVARCHAR(50),
GRP NVARCHAR(100)
);
INSERT INTO #temp
VALUES
('Prashanth',
'AB'
),
('Kiki',
'ABC'
),
('Steven',
'ABCD'
);
The following SQLREPLACE function repeats execution three times to get the desired result. The first input pattern "A" is evaluated and replaced with 5 if found. Then B is evaluated. If found, the number 9 will be replaced. Finally, D is replaced by 4.
SELECT Name,
GRP,
REPLACE (REPLACE (REPLACE(GRP, 'A', '5'), 'C', 9), 'D', 4) GRPCODE
FROM #temp;
Here is an example to update using the SQL REPLACE function. In this case, GRP column with the GRP CODE, run the following SQL.
UPDATE #temp
SET
GRP = replace(replace(REPLACE(GRP, 'A', '5'), 'C', 9), 'D', 4);
Now, let’s take a look at the data:
SELECT * FROM #temp;
Example 3: When working with raw data in SQL Server, we may need to format data and replace multiple characters in a string. Now let's take an example to understand this implementation.
For a presentation, see the following Contact column. And the contact numbers are in a specific format ["(xxx) xxxxxxx"] but for our work we need a contact number in (xxx xxxxxxx).
Now we will try to replace the characters `(` and `)` with an empty string using the following query.
SELECT Contact AS [Original ContactNumber],
REPLACE(REPLACE(Contact, '(', ''), ')', '') AS [Formatted ContactNumber]
FROM Sample;
We can easily replace multiple characters in SQL Server by using multiple Replace() functions in one another. In this example, we want to replace 2 characters [ ‘(‘ and ‘)‘] so we are using 2 Replace() functions. The Replace() function from the last will be executed first, and its result will be used as input for another Replace() function.
SQL Replace Null with 0
Example 1:
UPDATE [table]
SET [column]=0
WHERE [column] IS NULL;
Empty values can be replaced in SQL using UPDATE, SET, and WHERE to find nulls in a table column and replace them. In the above example, it replaces them with 0.
Example 2: The UPDATE command is a DML command as opposed to a DDL (Data Definition Language), DCL (Data Control Language) or TCL (Data Definition Language) command. transaction control language).) . This means it is used to modify pre-existing data. Other DML commands include: SELECT, SELECT, DELETE, etc.
UPDATE takes a table and uses the SET keyword to control which rows to modify and what values to assign to it. The WHERE keyword checks for a condition, and if true, the SET part is executed and the row is set to the new value. If false, it is not set to the new value.
To add 1 to every value in a column you can run:
UPDATE [table]
SET [column]=[column]+1;
From above example takes the values in a column and adds 1 to them.
Example 3: To set every value to a random integer on the interval [1,10]:
UPDATE [table]
SET [column]=1+random()*9::int;
Generates a random double precision (float8) type number from [0,1), multiplies it by 9, and adds 1 to that value and casts it to an integer type for each row.
Example 4: To set values to 0 for even 1 for odd:
UPDATE [table]
SET [column]=MOD([column],2);
Uses MOD to set the column values to the remainder of the column values divided by 2.
Summary:
- To replace Nulls with 0s use the UPDATE command.
- Can use filters to only edit certain rows within a column
Update can also be used for other problems like:
- Generating random data.
- Adding one to every row in a column (or where a condition is true).
- Setting Values based on if a column is even or odd.