RE: Finding Special Characters in Oracle Column

Toad 10.6.0.42, Oracle 11

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE     11.1.0.7.0             Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


From: [email protected] [mailto:[email protected]] On Behalf Of SCHROEDER, NATHAN E (AG/1000)
Sent: Tuesday, July 24, 2012 12:33 PM
To: [email protected]
Subject: [toad] RE: Finding Special Characters in Oracle Column


Huh! It doesn't, for me. Toad 11.0 (or SQL*Plus), Oracle 11.2.0.2.0.

Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. B2SB - Saint Louis, MO - 63167
314-694-2592

From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Lovelady, Dennis E.
Sent: Tuesday, July 24, 2012 11:19 AM
To: [email protected]<mailto:[email protected]>
Subject: [toad] RE: Finding Special Characters in Oracle Column

The following returns one row for me, in TOAD:

select 1 FROM DUAL where regexp_like ('ABC'||chr(9)||'DEF','[\t~*>]');


From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of SHIVANAND BHARTI
Sent: Tuesday, July 24, 2012 12:12 PM
To: [email protected]<mailto:[email protected]>
Subject: [toad] RE: Finding Special Characters in Oracle Column


Hi Dennis ... Here is some sample query to test with.


select 1 FROM DUAL where regexp_like ('ABC'||chr(9)||'DEF',chr(9));  -- Works
select 1 FROM DUAL where regexp_like ('ABC'||'~'||'DEF','[~*>]'); -- Works

I want to combine these two.

select 1 FROM DUAL where regexp_like ('ABC'||chr(9)||'~*'||'DEF','[\t]'); --Does not work.

From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Lovelady, Dennis E.
Sent: Tuesday, July 24, 2012 12:00 PM
To: '[email protected]'
Subject: [toad] RE: Finding Special Characters in Oracle Column


Sorry, I had nothing with which to test it; I wasn't the one with the problem.  Else my response would have been something like: "This works for me."

You might get by with pressing TAB after the [ character - that's worked for me in the past (in UNIX, though).  But that's horrible methodology from a documentation perspective, and TOAD - or something else - may manipulate that kind of tab.  But if it's a one-off, might be worth a try.

Everything I've read says that \t should work as a regex replacement for TAB.  But that's the beauty of standards: there are so many from which to choose.

Last thought on this: If you're doing this in an environment that can monkey with your \t (such as a shell command line), you may need to escape it like \\t<file:///\\t>

Dennis

From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of SHIVANAND BHARTI
Sent: Tuesday, July 24, 2012 11:54 AM
To: [email protected]<mailto:[email protected]>
Subject: [toad] RE: Finding Special Characters in Oracle Column
 No it does not.
Did you test it ?
 From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Lovelady, Dennis E.
Sent: Tuesday, July 24, 2012 10:32 AM
To: [email protected]<mailto:[email protected]>
Subject: [toad] RE: Finding Special Characters in Oracle Column
 This doesn't work for you?

select * from seasons where regexp_like (season_desc,'[\t~>*]');
 or
 select * from seasons where regexp_like (season_desc,'[\9~>*]');
From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of SHIVANAND BHARTI
Sent: Tuesday, July 24, 2012 10:26 AM
To: [email protected]<mailto:[email protected]>
Subject: [toad] Finding Special Characters in Oracle Column


Hi Folks ... I want to find all the records in a table( Oracle 10g)  which has TAB,~,>,* in a VARCHAR2 column.

I can do it in two separate query using regexp_like.
But I am not able to do it in one query using regexp_like.

select * from seasons where regexp_like (season_desc,'[~>*]');
select * from seasons where regexp_like (season_desc,chr(9));

Can you help me do so.

Thanks,
Shiva.

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled
to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and
all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its
subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of "Viruses" or other "Malware".
Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying
this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially
including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of
Treasury, Office of Foreign Asset Controls (OFAC).  As a recipient of this information you are obligated to comply with all
applicable U.S. export laws and regulations.



<BR>_____________________________________________________________
<FONT size=2><BR>
DTCC DISCLAIMER: This email and any files transmitted with it are
confidential and intended solely for the use of the individual or
entity to whom they are addressed. If you have received this email
in error, please notify us immediately and delete the email and any
attachments from your system. The recipient should check this email
and any attachments for the presence of viruses.  The company
accepts no liability for any damage caused by any virus transmitted
by this email.</FONT>