Home » SQL & PL/SQL » SQL & PL/SQL » pattern matching of table names
pattern matching of table names [message #195604] Fri, 29 September 2006 12:13 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
A schema contains many tables. I want to find out the list of table names having
a) Name containing CUSTACCT
b) The last two characters of the table name containing numbers.

For eg only tables having names CUSTACCT01, CUSTACCT03. Ignore everything else.

How to do it.?
Re: pattern matching of table names [message #195608 is a reply to message #195604] Fri, 29 September 2006 12:23 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
sql>select table_name from user_tables where table_name like '%CUSTACCT%';

TABLE_NAME
------------------------------
CUSTACCT
CUSTACCT01
CUSTACCT02
CUSTACCT99
CUSTACCTXX

5 rows selected.

sql>select table_name
  2    from user_tables
  3   where table_name like '%CUSTACCT%'
  4     and replace(
  5           translate(
  6             substr(table_name, length(table_name) - 1), '0123456789', '0000000000'), '0') is null;

TABLE_NAME
------------------------------
CUSTACCT01
CUSTACCT02
CUSTACCT99

3 rows selected.
Re: pattern matching of table names [message #195609 is a reply to message #195604] Fri, 29 September 2006 12:27 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks a ton..

Re: pattern matching of table names [message #195615 is a reply to message #195604] Fri, 29 September 2006 12:46 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
How do I change the above query such that I do the pattern matching based on a distinct year field of main table (CUSTACCT).

Eg if CUSTACCT table contains only data of year of 04, 05 and 06, the corresponding CUSTACCT04, CUSTACCT05 and CUSTACCT06 must be present.

Regards,
Re: pattern matching of table names [message #195630 is a reply to message #195608] Fri, 29 September 2006 14:49 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,
I tried doing this query, but it returns all tables whose last 2 characters are numeric. How do i just point into certain table names only.

select table_name
from user_tables
where substr(table_name,-2) in ( select distinct(substr(timstamp,8,9)) from CUSTACCT)
and replace(
translate(
substr(table_name, length(table_name) - 1), '0123456789', '0000000000'), '0') is null
Re: pattern matching of table names [message #195631 is a reply to message #195630] Fri, 29 September 2006 15:25 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
What happened to the '%CUSTACCT%' part of the query?
Re: pattern matching of table names [message #195632 is a reply to message #195631] Fri, 29 September 2006 15:31 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Basically I needed both,
1) The table name should be CUSTACCT%
2) The number at the end of the table name (03,04) must match with the distinct year field of a table in CUSTACCT. So that I dont create necessary tables unless there is no data for that year.
So I was trying out replacing the CUSTACCT part with a query.

Re: pattern matching of table names [message #195636 is a reply to message #195632] Fri, 29 September 2006 17:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Looks like Todd gave ya what you asked for, however now it looks like the requirements have changed.

SQL> select * from test1;

TABLE_NAME
--------------------
CUSTACCT01
CUSTACCT03
CUSTACCT04
CUSTACCT05
CUSTACCT06
CUSTACCTXX

6 rows selected.

SQL> select * from test1
  2  where table_name like 'CUSTACCT%'
  3  and substr(table_name, length(table_name) - 1) in ('04', '05', '06');

TABLE_NAME
--------------------
CUSTACCT04
CUSTACCT05
CUSTACCT06


If you are on 10g, you can use the following:

SQL> select * from test1
  2  where regexp_like(table_name,'^CUSTACCT(04|05|06)$');

TABLE_NAME
--------------------
CUSTACCT04
CUSTACCT05
CUSTACCT06

[Updated on: Fri, 29 September 2006 18:19]

Report message to a moderator

Re: pattern matching of table names [message #195666 is a reply to message #195632] Sat, 30 September 2006 13:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
yerics wrote on Fri, 29 September 2006 22:31

So that I dont create necessary tables unless there is no data for that year.


Alarmbell starts very quietly. Sounds like there could be one or two design-flaws here.
e.g. You don't create tables on the fly (at runtime) do you?
This should not be done in Oracle!

Just a warning..
Re: pattern matching of table names [message #195796 is a reply to message #195666] Mon, 02 October 2006 08:24 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Hi.

Tables are not created on the run. It is basically to alert if the table is not there and then the DBA will create the necessary tables and give privileges manually.

Thanks .

Previous Topic: ORA-02067 & ORA-00604
Next Topic: Optimisation problem
Goto Forum:
  


Current Time: Tue Dec 06 12:02:39 CST 2016

Total time taken to generate the page: 0.10080 seconds