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

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, sona.

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

Last updated: September 09, 2021 - 5:40 am 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 we 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

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