Remove quotes in field names with sql

[former_SQLA_member1694869]

For some reason, some field names in a table are escaped with quotes. The field names cannot be changed. What I wonder is if I via SQL with execute immediate statement etc., can remove these quotes in the field names?

Today it looks like this:

Select * from test

Mat----'Field1'----'Filed2'----'Field3'
--------------------------------------
AB01------xd---------ab----------aa--

But I want it to be this way:

Mat-----Field1-----Filed2-----Field3-
-------------------------------------
AB01------xd---------ab---------aa---

[Breck_Carter · Accepted solution]

CREATE TABLE test ( 
   Mat        VARCHAR ( 10 ),
   "'Field1'" VARCHAR ( 10 ),
   "'Filed2'" VARCHAR ( 10 ),
   "'Field3'" VARCHAR ( 10 ))
INSERT test VALUES ( 'AB01', 'xd', 'ab', 'aa' );
COMMIT;
Select * from test;
ALTER TABLE test RENAME "'Field1'" TO Field1;
ALTER TABLE test RENAME "'Filed2'" TO Filed2;
ALTER TABLE test RENAME "'Field3'" TO Field3;
Select * from test;

1 row(s) affected
Mat        'Field1'   'Filed2'   'Field3'   
---------- ---------- ---------- ---------- 
AB01       xd         ab         aa         
(1 rows)


Mat        Field1     Filed2     Field3     
---------- ---------- ---------- ---------- 
AB01       xd         ab         aa         
(1 rows)

[former_SQLA_member1694869 · Accepted solution]

Thanks!

I wrote wrong in the description. Sorry for that. I mean that the field names cannot be changed. But when I think about it, I can do that by first creating a temporary table. Then I can do according to your principle. However, there are hundreds of fields, can I make ALTER TABLE test RENAME more automated without entering the field names?

[Breck_Carter · Accepted solution]

This doesn't deal with "field names can't be changed", but it does take care of "hundreds of fields".

CREATE TABLE test ( 
   Mat        VARCHAR ( 10 ),
   "'Field1'" VARCHAR ( 10 ),
   "'Filed2'" VARCHAR ( 10 ),
   "'Field3'" VARCHAR ( 10 ))
INSERT test VALUES ( 'AB01', 'xd', 'ab', 'aa' );
COMMIT;
Select * from test;
BEGIN
DECLARE @SQL LONG VARCHAR;
FOR f_fetch AS c_fetch NO SCROLL CURSOR FOR
SELECT SYSTABCOL.column_name AS @column_name
  FROM SYSTAB 
          INNER JOIN SYSTABCOL
                  ON SYSTABCOL.table_id = SYSTAB.table_id
 WHERE SYSTAB.table_name = 'test' 
  AND USER_NAME ( SYSTAB.creator ) = 'DBA'
FOR READ ONLY
DO
   IF LEFT ( @column_name, 1 ) = '''' THEN
      SET @SQL = STRING (
         'ALTER TABLE test RENAME "', 
         @column_name, 
         '" TO ', 
         SUBSTR ( @column_name, 2, LENGTH ( @column_name ) - 2 ) );
      EXECUTE IMMEDIATE @sql;
   END IF;
END FOR;
END; 
Select * from test;
1 row(s) affected
Mat        'Field1'   'Filed2'   'Field3'   

AB01       xd         ab         aa       

(1 rows)
Mat        Field1     Filed2     Field3     

AB01       xd         ab         aa       

(1 rows)

[VolkerBarth · Accepted solution]

Certainly a better way than to live with weird column names in base tables...

[Vlad · Accepted solution]

The best solution I have ever read!!!! Just to fix the things, that were done wrong.

[former_SQLA_member1694869 · Accepted solution]

Many thanks! If I use a temporary table first via "into local temporary table". Can I access the temporary table via SYSTAB as well?

[VolkerBarth · Accepted solution]

No, local temporary tables are not contained in the system catalog. However, you can use select into with permanent tables, too.

[former_SQLA_member1694869 · Accepted solution]

Ok, then I don't get this logic to work with temporary tables. Right? Can I use something similar to replace the column names directly in a sql with EXECUTE IMMEDIATE?

[former_SQLA_member1694869 · Accepted solution]

Something like this:

declare @sql long varchar;

SET @SQL  = string('select ',(select list(if left(string(name), 1) = '''' then substr(string(name), 2, length(string(name) ) - 2) else string(name) endif ) from sa_describe_query('select * from test') ), ' from test');

execute immediate with result set on @sql; 
end;

But this does not work... Can I get results in a temporary table with execute immediate?

[Breck_Carter · Accepted solution]

You cannot use ALTER TABLE on a local temporary table.

[former_SQLA_member1694869 · Accepted solution]

Ok, I can do something similar and use local temporary table or directly in the sql?

[VolkerBarth · Accepted solution]

So what are you trying to achieve? I guess we might be more able to help on your actual goal instead of commenting on valid/invalid syntax...

[former_SQLA_member1694869 · Accepted solution]

Yes, you are right. I apologize for that. What I want to achieve is to remove quotes in the field name of my sql when these occur. The table contains the field names with quites, but I cannot change these. What I then thought was to first make a temporary table and use Breck's suggestions, but it does not work on temporary tables. So what I want is to remove quites from the field names in a sql.

[Breck_Carter · Accepted solution]

CREATE TABLE test ( 
   Mat        VARCHAR ( 10 ),
   "'Field1'" VARCHAR ( 10 ),
   "'Filed2'" VARCHAR ( 10 ),
   "'Field3'" VARCHAR ( 10 ))
INSERT test VALUES ( 'AB01', 'xd', 'ab', 'aa' );
COMMIT;
Select * from test;
CREATE VIEW vtest (
   Mat,
   Field1,
   Filed2,
   Field3 )
AS SELECT * FROM test;
Select * from vtest;

Mat        'Field1'   'Filed2'   'Field3'   
---------- ---------- ---------- ---------- 
AB01       xd         ab         aa         

Mat        Field1     Filed2     Field3     
---------- ---------- ---------- ---------- 
AB01       xd         ab         aa         

[Breck_Carter · Accepted solution]

With EXECUTE IMMEDIATE to CREATE VIEW...

CREATE TABLE test ( 
   Mat        VARCHAR ( 10 ),
   "'Field1'" VARCHAR ( 10 ),
   "'Filed2'" VARCHAR ( 10 ),
   "'Field3'" VARCHAR ( 10 ))
INSERT test VALUES ( 'AB01', 'xd', 'ab', 'aa' );
COMMIT;
Select * from test;

BEGIN
DECLARE @SQL LONG VARCHAR;
SELECT STRING ( 
          'CREATE VIEW vtest ( ',
          LIST ( IF LEFT ( SYSTABCOL.column_name, 1 ) = ''''
                    THEN SUBSTR ( SYSTABCOL.column_name, 
                                  2,  
                                  LENGTH ( SYSTABCOL.column_name ) - 2 )
                    ELSE SYSTABCOL.column_name
                 END IF,
                 ', '
                 ORDER BY SYSTABCOL.column_id ), -- column order 
          ' ) AS SELECT * FROM test' )
  INTO @sql
  FROM SYSTAB 
          INNER JOIN SYSTABCOL
                  ON SYSTABCOL.table_id = SYSTAB.table_id
 WHERE SYSTAB.table_name = 'test' 
  AND USER_NAME ( SYSTAB.creator ) = 'DBA';
SELECT @sql;
EXECUTE IMMEDIATE @sql;
END;
Select * from vtest;

Mat        'Field1'   'Filed2'   'Field3'   
---------- ---------- ---------- ---------- 
AB01       xd         ab         aa         

@sql                                                 
----------------------------------------------------------------------- 
CREATE VIEW vtest ( Mat, Field1, Filed2, Field3 ) AS SELECT * FROM test

Mat        Field1     Filed2     Field3     
---------- ---------- ---------- ---------- 
AB01       xd         ab         aa         

[VolkerBarth · Accepted solution]

With v17 you could also use TRIM to remove leading/trailing quotes, so it would just be

... LIST ( TRIM ( SYSTABCOL.column_name, ''''), ', ' ORDER BY...

[Breck_Carter · Accepted solution]

> TRIM ( string-expression [ , trim-char-set ] )

Awesome! ...that's gonna be useful.