A normalized database makes life simple either SQL or other types. Whether for work as a database administrator or for play, these wonders of technology organize our lives in more ways than we may realize.

We can store just about anything in a database. We just have to set up the right tables to store the data, and then use the right SQL statements to retrieve them again. Unfortunately, like any tool, databases have their limits. We can’t store everything under the sun in them. However, we can store most of the things we need in a database, and use the replace SQL keyword when we need something different.

Brush up on  the fundaments of SQL at Udemy

The Replace SQL Function

The REPLACE SQL keyword lets you change something. The REPLACE SQL function lets you change how your data will be displayed. By far the most common use of the keyword, you will most likely come across it in your database programming career.

Available with nearly every database system, REPLACE lets you replace one string with another. This new string will be an updated version of the old string with some substring switched with another string.

In computer speak; strings are just sequences of characters. These characters can include the letters of the alphabets, numbers, and any punctuation mark. We use string to let our programs speak to us or display passages, words, or phrases. For instance, this entire blog article was created using strings. Substrings are strings contained within other strings. The string “a string” is a substring of the string “This is a string.”

Top courses in SQL

Computers store strings as character arrays in their memory, and this lets us change these as we see fit. The REPLACE SQL function takes advantage of this system.

REPLACE takes in three strings as arguments and returns a string as a result.

REPLACE(string1, string2, string3)

String1 is our original string. String2 is the subtring we want replaced. String3 is the string that will replace string2 is the resultant string. This resultant string is represented by the REPLACE function itself. The following example shows how the REPLACE function works.

Let’s say your company has a database table called Geography that contains its major markets and their geographical location.

Table Geography

Region_NameMarket
EastBoston
EastNew York
WestLos Angeles
WestSan Diego

This table works great until we need to use different region names for a report. Instead of changing out table data, we can use the REPLACE SQL function to convert our region names to match the report requirements as we need them.

SQL students also learn

SELECT REPLACE(Region_Name, ‘ast’, ‘astern’) Region1 FROM Geography;

Result:

Region1
Eastern
Eastern
West
West

I wrote the SQL commands in capital letters per international convention, but you can use lower case letters as well.

select replace(Region_Name, ‘ast’, ‘astern’) Region1 from Geography;

That’s all you need to know to use the REPLACE SQL function. You give it the string you need changed along with the change you need made, and it will output the string with the changes. You can even use it in INSERT and UPDATE statements to translate your database table into another table to change its information.

INSERT Reports (Region_Name, Market) VALUES (REPLACE(Geography.Region_Name, ‘ast’, ‘astern’), Geography.Market);
UPDATE Geography SET Region_Name = REPLACE(Region_Name, ‘ast’, ‘astern’);

As REPLACE does not change your original string, you can play with it without causing any damage to your database. Feel free to play with it so you can become familiar with how it works and when to use it.