To: Venugopal V V S 
Subject: Substituting _ and % in SQL

> Hello Sir!
>
> How can I get the list of tables having an "under_score _" or a percentile
> "%" character as part of their name?
> Obviously I can't use 
> "where tname like '%%%' " or
> "where tname like '%_%' ".
> I hope I could have communicate the problem in much clear..
> I know these _ and % are wildcard chars..
> How can I go about it? What is the Escape character in SQL.
> 
> I know Ur busy with Ur own work!
> if U find time Pls.. let me know the solution..
> With Respect and Love..

Venu,

To see what the escape character is in SQL:

SHOW ESCAPE

To see all SQL parameters:

SHOW ALL

In my system, the default escape character is OFF. To set it to something
else:

SET ESCAPE #

Now # is the escape character. Now I can select the % literally:

SQL> SELECT '#%' FROM DUAL;

'
-
%

SQL>

To find all records with the % symbol, it is important to know the ASCII
codes. CHR(37) is the % symbol. To find all records with %:

SELECT * FROM table_name
WHERE INSTR(column_name,CHR(37)) > 0;

Same for the underscore, which is CHR(95). To see tables with underscore:

SELECT * FROM table_name
WHERE INSTR(column_name,CHR(95) > 0;


Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 340+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page http://www.arikaplan.com"> Back to Ari Kaplan's Home Page