SQL Standard

SQL Remove Characters from String

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:

REPLACE(original_value, from_value, to_value)

Note the search for a matching substring is case-sensitive.

We can illustrate how to use this function as shown:

SELECT REPLACE ('http://linuxhint.com', 'http', 'https');

The example code above replaces the string HTTP with HTTPS. This should return output as shown:




(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:

select first_name, last_name, replace(state, 'New York', '') as state from users;

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:

TRANSLATE(expression, source_characters, target_characters)

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:



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.


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:



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:



About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list