OraFAQ Forum: SQL & PL/SQL » Remove special characters from a string

Home » SQL & PL/SQL » SQL & PL/SQL » Remove special characters from a string

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

Remove special characters from a string [message #115687]

Wed, 13 April 2005 11:40

sudhakarp
Messages: 2
Registered: April 2005
Location: UK

Junior Member

Hi,
I have requirement in which I need to compare two strings.
Special characters in the string should be ignored in the comparison.
Special characters are: = + ( ) “ ” ! # . , - _ : ; / \ * ^ % £ $ ? [ ] < > @ & { }

Please advice.

Thanks in advance
Sudhakar

Report message to a moderator

Re: Remove special characters from a string [message #115702 is a reply to message #115687]

Wed, 13 April 2005 13:45

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

You can use the translate and replace functions to remove the special characters, then compare the results.

Report message to a moderator

Re: Remove special characters from a string [message #116096 is a reply to message #115687]

Sun, 17 April 2005 12:12

tvssbjagdish
Messages: 32
Registered: March 2005
Location: Hyderabad

Member

That goes here..

When u have the data like
col1
---------
abc 123
abc123
abc@#123
abc*(&)123

U can eliminate them by translate function
Select translate(upper(trim(col1)),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ ~!@#$%^&*()_+}{":?><`-=]['''';/.,','1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ') from mytable;

The output will be
ABC123
ABC123
ABC123
ABC123

Hope this helps
Jagdish

Report message to a moderator

Previous Topic:	multiple columns into rows
Next Topic:	NDS multiple row DML wthout using BULK COLLECT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 22 02:33:06 CDT 2025