AppDividend
Latest Code Tutorials

SQL Replace Function | Search and Replace String in Database

0

SQL replace function is used for replacing a sequence of characters in a string with another set of characters, i.e. it is used for replacing all the occurrences of a substring within a present string taken from another new string. SQL replace function performs case sensitive replacement in MySQL but in SQL Server it is case-insensitive. It returns NULL if any argument is NULL.

SQL replace function

Sometimes, you want to search and replace a substring with a new one in a column e.g., change a dead link to a new one, rename an obsolete product to the new name, etc.

SQL provides a very helpful string function called REPLACE that allows you to replace all the occurrences of the substring in the string with a new substring.

SQL replace function replaces all the occurrences of the substring within the string, with a new substring.

See the following syntax.

REPLACE(string, old_string, new_string)
  1. string: The string will be used for replacing the set of characters by another set of characters.
  2. old_string: The substring which will be searched in the string.
  3. new_string: All occurrences of old_string will be replaced with new_string in the string.

The function returns the new string.

#Query

SELECT REPLACE(“I Love SQL”, ’I’, ’Everyone’);

See the below output.

Everyone Love SQL

#EXPLANATION

In this example, it is very well clear that I was replaced with Everyone who replaced I love SQL to the desired output as shown above.

#Query

Let’s see another example.

SELECT REPLACE(“appdivIdend.com”, ‘i’, ‘3’);

See the output.

appd3vIdend.com

#EXPLANATION

Here, in the above example, i was replaced with 3. As mentioned above, It performs case sensitive replacement.

This will happen only in MySQL; in SQL server it is not case sensitive. We have also used the SELECT statement in the above query.

So, in that case, the output would have been appd3v3dend.com

#Query

See the following query.

SELECT REPLACE(“ABC DEF ABC”, ‘a’, ’B’);

See the output.

ABC DEF ABC

#EXPLANATION

Here there was no replacement because “a” was not present in the first string.

#Query

See the following query.

SELECT REPLACE('App Dividend.com', ' ', '_');

See the output.

App_Dividend.com

#EXPLANATION

Here, whitespace or blank space is replaced with an underscore.

#Query

See the following query.

SELECT REPLACE(‘AppDividend.com’, ‘i’, ‘123’);

See the output.

AppD123v123end.com

#EXPLANATION

Here, in the above example, i was replaced with 123.

Now, from the examples mentioned above, you might have got a clear picture of what SQL replace function does.

Let’s look at a proper example where it is used.

Suppose we have a table named STUDENT.

Roll Name City Phone
1 Rohit Patna 585.145.1475
2 Shouvik Jalandhar 225.856.4475
3 Shubh Kolkata 123.456.7890
4 Karan Allahabad 258.258.1452
5 Shivam Palampur 225.142.2589
6 Karan Dhulian 145.585.7854

 

Now, if we want to replace the (.) character with (-) character in the phone column. Then we have to use REPLACE function along with the UPDATE statement.

#QUERY

UPDATE STUDENT SET Phone = REPLACE(Phone, '.', '-');

See the output.

Roll Name City Phone
1 Rohit Patna 585-145-1475
2 Shouvik Jalandhar 225-856-4475
3 Shubh Kolkata 123-456-7890
4 Karan Allahabad 258-258-1452
5 Shivam Palampur 225-142-2589
6 Karan Dhulian 145-585-7854

 

So, here you can see that (.) character is replaced with (-) character.

Finally, SQL Replace Function Example | Replace Function In SQL is over.

Recommended Posts

SQL replicate function

SQL lower function

SQL left function

SQL difference function

SQL char function

SQL charindex function

SQL ascii function

SQL trim function

SQL substring function

SQL concat function

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.