remove first 3 characters from table name

[dsd7038]

I run a similar command on many tables where the table name differs but the structure is the same i.e U##table name = table name minus the first 3 characters U##. Is there a simpler way to extract all tables owned by user a where the table name is like U## and the upper table value is the U## table table minus the first 3 characters.

UPDATE cars
SET U##model_no = UPPER(model_no);


UPDATE trucks
SET U##license_no = UPPER(license_no);

[balajiyes]

Do you need to extract the first three characters...
from U##USER
to USER

Use SUBSTR('U##USER', 1, 3)

or

SELECT table_name FROM ALL_TABLES WHERE owner LIKE 'U##%';

[TimHall]

Hi.

In your question you are talking about manipulating table_names, but in your examples you seem to be manipulating column names. The following code will perform the actions shown in your examples:

BEGIN

  FOR c IN (SELECT table_name, column_name

            FROM   user_tab_columns

            WHERE  column_name like 'U##%')

  LOOP

    EXECUTE IMMEDIATE 'UPDATE ' || c.table_name || 

                      ' SET ' || c.column_name || ' = UPPER(' || SUBSTR(c.column_name, 4) || ')';

  END LOOP;

END;

/

If I've missed the point please clarify the question and I'll have another go

Cheers