Skip to Main Content
  • Questions
  • a sql code to remove all the special characters from a particular column of a table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, sona.

Asked: February 25, 2016 - 7:50 am UTC

Last updated: August 25, 2022 - 1:24 pm UTC

Version: oracle 10.1.2

Viewed 100K+ times! This question is

You Asked

a sql code to remove all the special characters from a particular column of a table .

for example :

iNPUT-ABC -D.E.F
OUTPUT ABC DEF

AND IF THERE IS TWO NAMES LIKE
ABC PRIVATE LTD
ONE SPACE SHOULD BE MAINTAINED BETWEEN 2 WORDS.

and Chris said...

There are a number of ways you could do this. If you want to remove all non-alphanumeric characters you could use a regular expresion:

create table t (nm varchar2(20));

insert into t values ('-ABC -D.E.F');
insert into t values ('ABC   PRIVATE  LTD');
commit;
select * from t;

NM                 
--------------------
-ABC -D.E.F         
ABC   PRIVATE  LTD

update t 
set    nm = regexp_replace(
         regexp_replace(nm, '[^A-Z0-9 ]', ''),
         ' {2,}', ' '
       );

select * from t;

NM                 
--------------------
ABC DEF             
ABC PRIVATE LTD


Or translate (if you have a list of special characters you want to remove):

update t 
set    nm = regexp_replace(
              translate(nm, ' -.', ' '), ' {2,}', ' '
            );

select * from t;

NM                 
--------------------
ABC DEF             
ABC PRIVATE LTD

Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Chris

sona sh, February 25, 2016 - 10:51 am UTC

Thanks a ton Chris,It is working fine now.If i will get any further additional add ons on the requirement .i will contact you.

Regards
sonam
Connor McDonald
February 25, 2016 - 11:16 am UTC

Glad we could help

sona sh, February 25, 2016 - 10:58 am UTC


Thanks Chris

sona sh, February 25, 2016 - 10:58 am UTC

Thanks a lot Chris,It is working fine now.

Thanks Chris

sona sh, February 25, 2016 - 11:01 am UTC

Thanks a lot Chris,It is working fine now.

Thanks Chris

sona sh, February 25, 2016 - 11:03 am UTC

Thanks a lot Chris,It is working fine now.

Thanks Chris

sona sh, February 25, 2016 - 11:04 am UTC

Thanks a lot Chris,It is working fine now.

Other Alternatives - Query to remove multiple SPACE

Rajeshwaran Jeyabal, February 25, 2016 - 12:51 pm UTC

Query to remove multiple SPACE using Regexp and non-Regexp versions.

drop table t purge;
create table t(x varchar2(80));
insert into t values('Archivos de   biologia      y medicina exp');
insert into t values('Archivos de     Psiquiatria');
insert into t values('Archivos de pediatria    del Uruguay');
insert into t values('Archivos dominicanos de pediatria');
insert into t values('Archivos espanoles   de urologia');
insert into t values('Archivos latinoame   ricanos de nutricion');
commit;
rajesh@ORA11G> select x,
  2    replace(replace(replace(x,' ','@ '),' @'),'@') as replace1,
  3    regexp_replace(x,'( ){2,}','\1') as replace2
  4  from t;

X                                                       REPLACE1                                        REPLACE2
------------------------------------------------------- ------------------------------------------------------- ---------------------------------
Archivos de   biologia      y medicina exp              Archivos de biologia y medicina exp             Archivos de biologia y medicina exp
Archivos de     Psiquiatria                             Archivos de Psiquiatria                         Archivos de Psiquiatria
Archivos de pediatria    del Uruguay                    Archivos de pediatria del Uruguay               Archivos de pediatria del Uruguay
Archivos dominicanos de pediatria                       Archivos dominicanos de pediatria               Archivos dominicanos de pediatria
Archivos espanoles   de urologia                        Archivos espanoles de urologia                  Archivos espanoles de urologia
Archivos latinoame   ricanos de nutricion               Archivos latinoame ricanos de nutricion         Archivos latinoame ricanos de nutricion

6 rows selected.

rajesh@ORA11G>

Chris Saxon
February 25, 2016 - 5:53 pm UTC

Assuming that @ isn't a character you need to keep of course! ;)

Thanks Chris

sona sh, February 25, 2016 - 2:18 pm UTC

Thanks a lot.It is working Fine now.

Thanks Chris

sona sh, March 08, 2016 - 11:36 am UTC

Hi Chris,

I am working on a project which is based on sql and sql script actually.Can you please guide me on the same.
Can you please tell me from where i can read and try writing the query effeciently.

Thanks & Regards
Sonam
Chris Saxon
March 08, 2016 - 5:26 pm UTC

I'm not sure what you're looking for. If you have a new question then please post a new one rather than asking more here.

Likitha, October 02, 2017 - 8:07 pm UTC

Very helpful...Thanks a lot....!!!

control characters from text and special characters and exceptions

Imran, February 14, 2019 - 12:14 pm UTC

Hello Chris,

I am still having problems in finding the correct query in Oracle Output that can show me in a particular column ex. 3 columns in a table like first_name, last_name and full_name:

first_name last_name full_name
-------------------------------

1.John Smith John Smith
2.João Sm!?th João Sm!?th
3.João Smith João Smith
4.John Smíth John Smíth
5.J?hn Sm|ith J?hn Sm|th

-----------------------------------------
Output: Should be:
1.João Sm!?th João Sm!?th
2.J?hn Sm|ith J?hn Sm|th


Permitted Characters (Below):
-----------------------------
ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ'
ACEIOUAEIOUAEIOUAOEU, A-Z, a-z, 0-9


Should NOT be included such characters(Below):
----------------------------------------------
Restricted Like
! Like
" Like
# Like
& Like
( Like
) Like
, Like
: Like
; Like
? Like
[ Like
] Like
` Like
| Like


I saw your post here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526745900346594796
but couldn´t still find out the solution.
Chris Saxon
February 21, 2019 - 5:31 pm UTC

I'm not sure exactly what you're asking here. Please provide a test case in the form of:

create table
insert into
expected results

Anil kumar, July 30, 2019 - 11:22 am UTC

How to keep [] in result, as [] are not a special characters.
as you sql REGEXP_REPLACE(REGEXP_REPLACE(NM, '[^a-zA-Z0-9`~!@#$%^&*()_+-=|\}{:";'',./<>? ]', ''), ' {2,}', ' ') removed [] characters from results.
Chris Saxon
July 30, 2019 - 1:04 pm UTC

You're replacing any character which is NOT in the list. Square brackets aren't in the list!

If you want to keep these, you need to add them to the regex.

Geeting special character in place of original character

Sitaram, August 28, 2019 - 2:13 pm UTC

I have character like '-' and '?' in my source .but when i am loading in to target (oracle DB),its coming as '[]' and '!'.
how to handle it in sql query .so the same value will be updated in Target.
Chris Saxon
August 29, 2019 - 10:17 am UTC

How exactly are you doing that?

Double translate option

Mark Wooldridge, August 29, 2019 - 5:55 pm UTC

Years ago I found a post on this site where a double translate was used to remove bad characters from a string. I have used this function many times over the years.

The assumption is you know the list of characters that are good/acceptable and have 1 character you know is bad (inner translate removes all of the good characters leaving only the bad).

function remove_bad_chars(p_str     in varchar2,
                          p_good_chars    in varchar2,
                          p_bad_char    in char)
  return varchar2 is

begin
 return translate(p_str, substr(p_good_chars, 1, 1)||translate(p_str, p_bad_char||p_good_chars,  p_bad_char),  substr(p_good_chars, 1, 1));

end remove_bad_chars;

Chris Saxon
September 02, 2019 - 10:04 am UTC

Thanks for digging this out.

Remove extra spaces using recursive with

Mark Wooldridge, August 29, 2019 - 6:21 pm UTC

How about a recursive with?

var s varchar2(100)
exec :s := 'this    is    a   test   to  remove extra         spaces';
exec :s := 'ABC   PRIVATE  LTD';

  with q(str) as (
select :s
  from dual
 union all
select replace(str, '  ', ' ')
  from q
 where instr(str, '  ') > 0)
select trim(str) --** trim to remove any leading/trailing spaces
  from q
 where instr(str, '  ') = 0
/

Chris Saxon
September 02, 2019 - 10:09 am UTC

Cool, but I prefer the "double translate" method you posted before. Depending on what you're doing and the input, you could end up running lots of recursive branches.

nice and clean solution

Ying Wang, April 13, 2021 - 2:00 pm UTC

This is way much better than translate. Thank you so much Chris!
Chris Saxon
April 13, 2021 - 4:26 pm UTC

You're welcome

special characters

Tanis, September 08, 2021 - 4:00 pm UTC

select regexp_replace('TaqMan*^?!% Universal PCR Master Mix','[^'||chr(1)||'-'||chr(127)||']', '|') from dual;
Connor McDonald
September 09, 2021 - 5:40 am UTC

Interesting string choice :-)

Regrading About Removing of Special Characters,Spaces,and Numeric Value in a string

sai, January 31, 2022 - 7:01 am UTC

Hi,
How Removing of Special Characters, Spaces, and Numeric Value in a string

EX:123$+james*+-$%@gmail.com

Output:James
Chris Saxon
January 31, 2022 - 11:15 am UTC

You could replace everything that's NOT a letter, e.g.:

select regexp_replace ( 
         '123$+james*+-$%@gmail.com', 
         '[^[:alpha:]]' 
       ) str
from   dual

STR          
-------------
jamesgmailcom

Replacing special characters with their equivalent

SK, April 20, 2022 - 5:17 pm UTC

Hi ..I have a string JOSÉ and need it to be replaced as JOSE. similarly for other such characters like Á, Î . Is there a way to do this in oracle 12 plsql?
Chris Saxon
April 21, 2022 - 12:22 pm UTC

You can use REPLACE as with any other substitution.

The CONVERT function maps from one character set to another, so may help in some cases. But it can give unexpected outcomes if the conversion is not possible:

SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII' ) 
FROM   DUAL; 

A E I ? ? A B C D E 

What would be the oracle procedure to validate string with special chars

Arun, August 25, 2022 - 12:18 pm UTC

Hi Chris, I have gone through your responses, which were amazing,
Hope you help me out here,
So,In columns like First name, Middle name and Last name, if we have already permitted special characters while storing data without validation. so, now if we want to validate that data to restrict special chars, to be stored in another table/upcoming inputs. for the string which have special chars the alerts should be like, "this string have special characters". and string without special chars are allowed to get stored in the table.

NOTE: I'm not anticipating that special char string should eliminate the special chars, only I need validate to deliver the "msg". I need oracle procedure/sql query. Kindly help.
Chris Saxon
August 25, 2022 - 1:24 pm UTC

You could do a variation of one of the above solutions - remove everything which is a letter. If the resulting string has characters => they're special => raise an error