Home » SQL & PL/SQL » SQL & PL/SQL » Removal of Special characters in a string (10g)
Removal of Special characters in a string [message #573682] Wed, 02 January 2013 05:40 Go to next message
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.
Re: Removal of Special characters in a string [message #573684 is a reply to message #573682] Wed, 02 January 2013 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No need of REPLACE:
SQL> select translate('ABCD $ & * # 123 Test','A$&*# ','A') result from dual;
RESULT
-----------
ABCD123Test

Regards
Michel
Re: Removal of Special characters in a string [message #573685 is a reply to message #573682] Wed, 02 January 2013 05:54 Go to previous messageGo to next message
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..

Re: Removal of Special characters in a string [message #573687 is a reply to message #573685] Wed, 02 January 2013 05:57 Go to previous messageGo to next message
Littlefoot
Messages: 19600
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.
Re: Removal of Special characters in a string [message #573688 is a reply to message #573685] Wed, 02 January 2013 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Removal of Special characters in a string [message #573690 is a reply to message #573684] Wed, 02 January 2013 06:08 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
How about the string 'ABCD $ & * # 123 &Test'
Re: Removal of Special characters in a string [message #573691 is a reply to message #573690] Wed, 02 January 2013 06:10 Go to previous messageGo to next message
Littlefoot
Messages: 19600
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What about it?
Re: Removal of Special characters in a string [message #573722 is a reply to message #573682] Wed, 02 January 2013 15:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
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.
Re: Removal of Special characters in a string [message #573757 is a reply to message #573722] Thu, 03 January 2013 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Removal of Special characters in a string [message #573767 is a reply to message #573757] Thu, 03 January 2013 01:51 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi Everyone,
Thanks a lot.It really helped me a lot. Smile
Re: Removal of Special characters in a string [message #573768 is a reply to message #573767] Thu, 03 January 2013 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what is the specification?
Which characters are allowed? Or which ones are forbidden?

Regards
Michel
Re: Removal of Special characters in a string [message #573772 is a reply to message #573768] Thu, 03 January 2013 01:59 Go to previous messageGo to next message
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.
Re: Removal of Special characters in a string [message #573774 is a reply to message #573772] Thu, 03 January 2013 02:05 Go to previous messageGo to next message
Littlefoot
Messages: 19600
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?
Re: Removal of Special characters in a string [message #573787 is a reply to message #573774] Thu, 03 January 2013 03:06 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Thank you ..
Yes,the Problem got solved..
Re: Removal of Special characters in a string [message #573791 is a reply to message #573787] Thu, 03 January 2013 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So post the final solution...

Regards
Michel
Re: Removal of Special characters in a string [message #573813 is a reply to message #573791] Thu, 03 January 2013 05:34 Go to previous messageGo to next message
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

Re: Removal of Special characters in a string [message #573818 is a reply to message #573813] Thu, 03 January 2013 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Removal of Special characters in a string [message #574125 is a reply to message #573682] Mon, 07 January 2013 06:12 Go to previous message
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; 
Previous Topic: Query Generate an Error Pls Guide me Where I am Wrong
Next Topic: Count characters
Goto Forum:
  


Current Time: Mon Sep 15 22:11:35 CDT 2014

Total time taken to generate the page: 0.11164 seconds