OraFAQ Forum: SQL & PL/SQL » Removal of Special characters in a string

Home » SQL & PL/SQL » SQL & PL/SQL » Removal of Special characters in a string (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Removal of Special characters in a string [message #573682]

Wed, 02 January 2013 05:40

userora
Messages: 63
Registered: December 2012
Location: Chennai

Member

Hi All,

I am looking for a SQL query which checks for special character(s) in a string and remove them if any.

for e.g: input string : 'ABCD $ & * # 123 Test'
expected output should be in upper case : 'ABCD123TEST'

Please note, we do not know the position of special characters and also what kind of special characters will come as input

Can anyone please provide me a query/Solution for this?

Thanks in Advance.

Report message to a moderator

Re: Removal of Special characters in a string [message #573684 is a reply to message #573682]

Wed, 02 January 2013 05:50

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No need of REPLACE:

SQL> select translate('ABCD $ & * # 123 Test','A$&*# ','A') result from dual;
RESULT
-----------
ABCD123Test

Regards
Michel

Report message to a moderator

Re: Removal of Special characters in a string [message #573685 is a reply to message #573682]

Wed, 02 January 2013 05:54

userora
Messages: 63
Registered: December 2012
Location: Chennai

Member

Hi Thank you,
But my concern is that i doesn't know what type of special characters will come as input.
Actually i need to do bulk insert like this into a table...

for eg.
Record 1 : 'ABCD# % ^'
Record 1 : 'abcd& * @ 123'
Record 1 : 'asdd @# sdfds'
Record 1 : 'sdfsd ( ) _ dfgdf'
Record 1 : 'sdfs #$% dfsd123'

in this each and every record has different kinds of special strings..

Report message to a moderator

Re: Removal of Special characters in a string [message #573687 is a reply to message #573685]

Wed, 02 January 2013 05:57

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

That is not a problem; just name them all as the second parameter of the REPLACE function.

Report message to a moderator

Re: Removal of Special characters in a string [message #573688 is a reply to message #573685]

Wed, 02 January 2013 05:59

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to either list the forbidden characters either list the allowed characters.
So give one of the list.

Regards
Michel

Report message to a moderator

Re: Removal of Special characters in a string [message #573690 is a reply to message #573684]

Wed, 02 January 2013 06:08

ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore

Senior Member

How about the string 'ABCD $ & * # 123 &Test'

Report message to a moderator

Re: Removal of Special characters in a string [message #573691 is a reply to message #573690]

Wed, 02 January 2013 06:10

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What about it?

Report message to a moderator

Re: Removal of Special characters in a string [message #573722 is a reply to message #573682]

Wed, 02 January 2013 15:16

Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA

Senior Member

So you you want to remove everything but letters and digits, right? If so:

SQL> select  regexp_replace('ABCD $ & * # 123 Test','[^[:alnum:]]') str
  2    from  dual
  3  /

STR
-----------
ABCD123Test

SQL>

And if you want to remove all puctuation characters plus space:

select  regexp_replace('ABCD $ & * # 123 Test','[[:punct:] ]',null) str
  from  dual
/

STR
-----------
ABCD123Test

SQL>

SY.

Report message to a moderator

Re: Removal of Special characters in a string [message #573757 is a reply to message #573722]

Thu, 03 January 2013 00:58

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

So you you want to remove everything but letters and digits, right?

Maybe, we are still waiting for the list or specification.

Regards
Michel

Report message to a moderator

Re: Removal of Special characters in a string [message #573767 is a reply to message #573757]

Thu, 03 January 2013 01:51

userora
Messages: 63
Registered: December 2012
Location: Chennai

Member

Hi Everyone,
Thanks a lot.It really helped me a lot. Smile

Smile

Report message to a moderator

Re: Removal of Special characters in a string [message #573768 is a reply to message #573767]

Thu, 03 January 2013 01:54

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So what is the specification?
Which characters are allowed? Or which ones are forbidden?

Regards
Michel

Report message to a moderator

Re: Removal of Special characters in a string [message #573772 is a reply to message #573768]

Thu, 03 January 2013 01:59

userora
Messages: 63
Registered: December 2012
Location: Chennai

Member

Hi, The Forbidden characters are only the special characters and space in a string.
So,rest of characters should be considered.

Report message to a moderator

Re: Removal of Special characters in a string [message #573774 is a reply to message #573772]

Thu, 03 January 2013 02:05

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As you (and only you) know what characters are "special", and knowing that you got several options to do what you need, I believe that you, actually, solved the problem. Right?

Report message to a moderator

Re: Removal of Special characters in a string [message #573787 is a reply to message #573774]

Thu, 03 January 2013 03:06

userora
Messages: 63
Registered: December 2012
Location: Chennai

Member

Thank you ..
Yes,the Problem got solved..

Report message to a moderator

Re: Removal of Special characters in a string [message #573791 is a reply to message #573787]

Thu, 03 January 2013 03:35

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So post the final solution...

Regards
Michel

Report message to a moderator

Re: Removal of Special characters in a string [message #573813 is a reply to message #573791]

Thu, 03 January 2013 05:34

userora
Messages: 63
Registered: December 2012
Location: Chennai

Member

Hi.

My input will be like this in the table.

select * from string_replace
----------------------------
Record 1 - ABCD % ! ^ # Test
Record 2 - ABCD $ & * #^ # 123Test2
Record 3 - ABCD $ & *#! ^ Test
Record 4 - ABCD!@#$%^&*(")_+-={}| \[];:<>?/.12345Test
Record 5 - nithya''s123}
Record 6 - ABCD $ & * # % ! ^ # 123 Test
Record 7 - ABCD $ % ! ^ # 123Test
Record 8 - ABCD!@#$%^&*(,")_+-={}|\[];:<>?/.12345

And the Solution and the result set as follows.

1) select regexp_replace(upper(name),'[^[:alnum:]]') str
from string_replace

2) select upper(replace(translate (name, '!@#$%^''''&*()_+=-{}[]|\/?><,.":;', ' '), ' ', ''))
from string_replace;

Result set
----------
Record 1 - ABCDTEST
Record 2 - ABCD123TEST2
Record 3 - ABCDTEST
Record 4 - ABCD12345TEST
Record 5 - NITHYAS123
Record 6 - ABCD123TEST
Record 7 - ABCD123TEST
Record 8 - ABCD12345

Report message to a moderator

Re: Removal of Special characters in a string [message #573818 is a reply to message #573813]

Thu, 03 January 2013 06:11

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So you have a list of forbidden characters.

Note that 1) and 2) does not return the same result.
And for 2), as I showed, you don't need REPLACE.

Regards
Michel

Report message to a moderator

Re: Removal of Special characters in a string [message #574125 is a reply to message #573682]

Mon, 07 January 2013 06:12

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Try like...

 select count(*), b.item ITEM
      from ( select a.ITEM
               from (select substr( :X,level,1 ) ITEM
               from dual connect by level < length(:X) + 1
                 ) a
           ) b
     group by b.item
     order by b.item;

Report message to a moderator

Previous Topic:	Query Generate an Error Pls Guide me Where I am Wrong
Next Topic:	Count characters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 20 10:25:38 CDT 2026