Home » SQL & PL/SQL » SQL & PL/SQL » How to detect the special characters in string
icon5.gif  How to detect the special characters in string [message #248459] Fri, 29 June 2007 02:13 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi!

I'm having a problem with a string. Basically the string is an output of some function and that may contains some special character which is not shown in the string - for example - chr(10)/chr(13) etc.

Is there any method or procedure that can detect all the special character inside that string and remove them all other than all the characters and digitis which is in form of character? I'm using oracle 9i.

Thanks in advance for your reply.
Re: How to detect the special characters in string [message #248468 is a reply to message #248459] Fri, 29 June 2007 02:33 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And your search here didn't reveal a single thread on the same subject? You did search, right?

SQL> SELECT banner
  2  FROM   v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> WITH yourtable AS
  2  (
  3    SELECT 'ab'||chr(10)||'cde223'              theval FROM dual UNION ALL
  4    SELECT 'ab'||chr(13)||'cde'||chr(10)||'223' theval FROM dual UNION ALL
  5    SELECT 'ab^cde^223'                         theval FROM dual UNION ALL
  6    SELECT 'ab-_=cde*-//-223'                   theval FROM dual
  7  )
  8  SELECT theval
  9  FROM yourtable
 10  /

THEVAL
----------------
ab
cde223

cde
223

ab^cde^223
ab-_=cde*-//-223

SQL>
SQL> WITH yourtable AS
  2  (
  3    SELECT 'ab'||chr(10)||'cde223'              theval FROM dual UNION ALL
  4    SELECT 'ab'||chr(13)||'cde'||chr(10)||'223' theval FROM dual UNION ALL
  5    SELECT 'ab^cde^223'                         theval FROM dual UNION ALL
  6    SELECT 'ab-_=cde*-//-223'                   theval FROM dual
  7  )
  8  SELECT REGEXP_REPLACE(theval,'[^[:alnum:]]', NULL) stripped
  9  FROM yourtable
 10  /

STRIPPED
--------------------------------------------------------------------------------

abcde223
abcde223
abcde223
abcde223

SQL>


MHE
Re: How to detect the special characters in string [message #248477 is a reply to message #248468] Fri, 29 June 2007 02:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
the OP's on Oracle 9...

For Oracle 9, you have to explicitly define what you see as special characters. Then use translate to get rid of them.

[Updated on: Fri, 29 June 2007 02:50]

Report message to a moderator

Re: How to detect the special characters in string [message #248479 is a reply to message #248477] Fri, 29 June 2007 02:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sad For this one time I thought I could use regular expressions...

MHE
Re: How to detect the special characters in string [message #248495 is a reply to message #248468] Fri, 29 June 2007 03:47 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Quote:
SELECT REGEXP_REPLACE(theval,'[^[:alnum:]]', NULL) stripped FROM yourtable


Maheer I cant undestand this quotes
Quote:
REGEXP_REPLACE(theval,'[^[:alnum:]]', NULL)
..please explain..Or any other way to solve this problem..can i do by using functions?


Re: How to detect the special characters in string [message #248498 is a reply to message #248495] Fri, 29 June 2007 03:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
REGEXP_REPLACE uses regular expressions. But, as Frank pointed out, you can't use this on Oracle 9i. Follow the advice given:

- Use translate
- Search the board and find this thread. Or this one.

There are many more threads about this particular issue.

MHE

Edit: translate, Maarten, not replace...

[Updated on: Fri, 29 June 2007 04:10]

Report message to a moderator

Re: How to detect the special characters in string [message #248501 is a reply to message #248459] Fri, 29 June 2007 04:06 Go to previous message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
thank u guys,

I have got solution

Quote:
SELECT TRIM(TRANSLATE('ABCD123!@#$%^&*()',TRIM(TRANSLATE('ABCD123!@#$%^&*()','ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',' ')),' '))FROM DUAL
Previous Topic: Query to rank
Next Topic: Size of Packege/Procedure
Goto Forum:
  


Current Time: Sat Dec 10 05:06:06 CST 2016

Total time taken to generate the page: 0.07339 seconds