SQL REPLACE() Function

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.



Sql replace function using sql server replace string, replace character, search and replace, replace with multiple characters and patterns, single quotes, find substring replace, part of string.

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


Sql server replace using replace string in field, sql server replace multiple characters, replace a string in a column, string functions in sql, search text replace all, case sensitive, first and last occurence, special characters, beteen 2 characters, null with 0.

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:

Fred (10 points)

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:

Msg 9828, Level 16, State 1, Line 1

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.