Home » SQL & PL/SQL » SQL & PL/SQL » How do I find special characters using a regular expresion?
How do I find special characters using a regular expresion? [message #190155] Tue, 29 August 2006 07:52 Go to next message
Fred Easey
Messages: 73
Registered: January 2005
Member

Hi,

What is the syntax if I want to return all values from a column where the string contains any special characters?

Actually even better than that would be if it returned a distinct list of all of the special characters found in the column.

Cheers,

Fred
Re: How do I find special characters using a regular expresion? [message #190159 is a reply to message #190155] Tue, 29 August 2006 08:31 Go to previous messageGo to next message
sabrieker
Messages: 8
Registered: August 2006
Location: turket
Junior Member
what do you mean with special characters
Re: How do I find special characters using a regular expresion? [message #190162 is a reply to message #190159] Tue, 29 August 2006 08:46 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
is this you want??

SELECT * FROM TABLE_NAME 
WHERE 
LENGTH(REPLACE(TRANSLATE(str,'asdfghjklqwertyuiopzxcvbnm1234567890','                                   '),' ',''))>0


Thanks,
Thangam
Re: How do I find special characters using a regular expresion? [message #190163 is a reply to message #190155] Tue, 29 August 2006 08:49 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from special_char;

SCHAR
-------
abc$
abc
13489
kos*
289#


SQL> select * from special_char
  2  where regexp_like(schar,'[^[:alnum:]]');

SCHAR
---------
abc$
kos*
289#
Re: How do I find special characters using a regular expresion? [message #190164 is a reply to message #190163] Tue, 29 August 2006 08:51 Go to previous message
Fred Easey
Messages: 73
Registered: January 2005
Member

I ended up doing this. Does this look right?

drop table fe_special_characters;

create table fe_special_characters
   (
   why char(1),
   ascii_code number(3)
   );

DECLARE

   strPokerAlias ods.tbl_crypto_accounts.username%TYPE;
   i int;
   strWhy char(1) := 'X';
   strMaster varchar2(100);

   /* Cursor declaration: */

   CURSOR T1Cursor IS

   SELECT 
      ods.tbl_crypto_accounts.username
   FROM
      ods.tbl_crypto_accounts
   WHERE
      username is not null and
      REGEXP_INSTR(username,'[^a-z^A-Z^0-9]') > 0;

   BEGIN

   OPEN T1Cursor;

   LOOP

      FETCH T1Cursor INTO strPokerAlias;

      EXIT WHEN T1Cursor%NOTFOUND;

           FOR i in 1..length(strPokerAlias) LOOP
   
               strWhy := substr(strPokerAlias,i,1);
               
               IF REGEXP_INSTR(strWhy,'[^a-z^A-Z^0-9]') > 0 
               
               THEN 
                                                
                  IF nvl(instr(strMaster,strWhy,1,1),0) = 0
               
                  THEN strMaster := strMaster||strWhy;
                  
                  insert into fe_special_characters values(strWhy,ascii(strWhy));
                     
                  END IF;
                  
               END IF;
               
           END LOOP;

      commit;
            
      END LOOP;

      CLOSE T1Cursor;

   END;
/   

select * from fe_special_characters;


[Updated on: Tue, 29 August 2006 08:52]

Report message to a moderator

Previous Topic: regular expression problem
Next Topic: How to store files from clob column to local path
Goto Forum:
  


Current Time: Sat Dec 10 22:15:34 CST 2016

Total time taken to generate the page: 0.08374 seconds