SQL Remove Characters From String

Method 1 - 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://cloudenv.io', 'http', 'https');

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

replace
-----------------------
https://cloudenv.io
(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.

Method 2 - 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.

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

url
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://cloudenv.io/sql-trim-function

Consider the example query below:

SELECT
TRIM('htts://cloudenv.io', 'https://') AS url;

The above example should remove the trailing https:// characters from the string. This should return:

url
cloudenv.io

Final Thoughts

Using the replace, translate, and trim functions is a great way to remove specific characters from a string.