How to Replace Special Characters in Oracle SQL

Options for Replacing Special Characters

In Oracle SQL, you have three options for replacing special characters:

• Using the REPLACE function
• Using the REGEXP_REPLACE function
• Using the TRANSLATE function

Each of them has their pros and cons.

REPLACE allows you to replace a single character in a string, and is probably the simplest of the three methods. The drawback is that it only allows you to replace one character. If you want to replace multiple, you can use nested functions, which can get messy.

REGEXP_REPLACE uses regular expressions to replace characters. It’s flexible and allows for multiple characters, but there’s a bit of a learning curve with regular expressions.

TRANSLATE is similar to REPLACE, but it allows for multiple characters to be replaced in a single function.

Let’s take a look at each of them, as well as the special characters to replace.

What Are Special Characters?

The special characters I’m referring to are any characters that aren’t alphanumeric.

Actually, you can define the characters you want to remove in these functions.

Perhaps it’s only the carriage return and new line characters. Or maybe it’s symbols such as # and !.

To check for the carriage return, use the CHR(13) function. To find the newline character, use CHR(10).

Using REPLACE

You can replace special characters using the Oracle REPLACE function.

For example, to replace a carriage return with a space:

1

REPLACE(your_column, CHR(13), ' ')  

To replace both carriage return and new line characters, you must use nested REPLACE functions. 

1

REPLACE(REPLACE(your_column, CHR(13), ' '), CHR(10), ' ')  

If you want to replace a lot of special characters, using many nested REPLACE functions can get messy and could have performance impacts.

There are a couple of other ways though.

Using TRANSLATE

The TRANSLATE function is similar to REPLACE, but it allows you to replace multiple characters at once, in one function.

It allows you to specify a character to search for, and a character to replace it with.

To replace special characters with TRANSLATE, you might need to specify the replacement character many times.

Let’s see an example.

1

TRANSLATE(your_column, CHR(10) || CHR(13, '  ')  

This function will replace the first character of the second parameter (CHR(10)) with the first character of the third parameter (a space). It will then replace the second character of the second parameter (CHR(13)) with the second character of the third parameter (another space).

If you want to replace a long list of characters, you’ll need to specify a replacement character each time.

1

TRANSLATE(your_column, '!@#$%^&*()', '          ')  

There are 10 characters in the second parameter, so there needs to be 10 characters in the third parameter.
Using REGEXP_REPLACE

You can also use the REGEXP_REPLACE function to replace special characters.

It’s more powerful than the REPLACE and TRANSLATE functions, but you need to understand regular expressions to be able to use it.

If you want to just remove all special characters, you can use a function like this:

1
2

SELECT REGEXP_REPLACE(your_column, '[^0-9A-Za-z]', '')  
FROM table; 

This function takes three parameters.

First, it needs the value that contains your string, in this case it’s your_column again.

Then, it has a regular expression in the second parameter. In this example, it means all characters that don’t match numbers or letters.

The third parameter is the character to replace any matching characters with.

So, this example replaces all characters that aren’t numbers or letters with a zero-length string. You can change this, of course.

So, that’s how you can replace special characters in Oracle SQL. You can use one of these three functions.

Reference: https://community.oracle.com/blogs/bbrumm/2016/12/11/how-to-replace-special-characters-in-oracle-sql