This article will learn how to remove a character from a string using Standard SQL and other database engines.
SQL Replace() Function
The replace() function is part of Standard SQL and replaces all occurrences in a string. The function syntax is as shown:
Note the search for a matching substring is case-sensitive.
We can illustrate how to use this function as shown:
The example code above replaces the string HTTP with HTTPS. This should return output as shown:
-----------------------
https://linuxhint.com
(1 row)
Consider the table shown below:
If we wish to remove the ‘New York’ record from the table, we can run a query as shown:
The above query should remove the string ‘New York’ from the table. The resulting set is as shown:
Notice the New York records have been removed.
SQL Translate Function
In Standard SQL, we can use the translate() function to remove a character from a string. The function syntax is as shown:
The function will replace each character specified in the source_characters parameter with the corresponding target_characters.
TRANSLATE('https://google.com', 'htps:/', '') AS url;
Note something different in the query above?
It behaves closely similar to the replace(0 function. However, the function does not allow duplicate characters in the source_characters parameter.
Hence, to remove ‘https://’, we specify the source characters as ‘htps:/’
The code above should return:
google.com
SQL Trim Function
Another function you can use to remove characters from a string is the trim() function. The trim function will remove all leading and trailing whitespace characters from a string by default.
You can learn more in the resource below.
https://linuxhint.com/sql-trim-function
Consider the example query below:
TRIM('htts://linuxhint.com', 'https://') AS url;
The above example should remove the trailing ‘https://’ characters from the string. This should return:
linuxhint.com
Final Thoughts
Using the replace, translate, and trim functions is a great way to remove specific characters from a string. Although the functions do not cover all aspects, you will find them helpful in various scenarios.
Enjoy databases!!
Want to explore more SQL tutorials, consider the resources below:
