Remove quotes in field names with sql

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---

execute execute-immediate

asked 21 Feb '20, 10:08

Rolle
558●49●51●61
accept rate: 0%

edited 21 Feb '20, 14:50

flat view

One Answer:

active answers oldest answers newest answers popular answers

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)

permanent link

answered 21 Feb '20, 14:40

Breck Carter
32.5k●540●724●1050
accept rate: 20%

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?

(21 Feb '20, 14:55) Rolle

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)

(22 Feb '20, 10:39) Breck Carter

Replies hidden

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

(22 Feb '20, 13:47) Volker Barth

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

(23 Feb '20, 11:31) Vlad

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

(23 Feb '20, 16:09) Rolle

Replies hidden

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

(23 Feb '20, 16:24) Volker Barth

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?

(24 Feb '20, 03:42) Rolle

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?

(24 Feb '20, 04:05) Rolle

You cannot use ALTER TABLE on a local temporary table.

(24 Feb '20, 08:16) Breck Carter

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

(24 Feb '20, 08:34) Rolle

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...

(24 Feb '20, 11:44) Volker Barth

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.

(24 Feb '20, 13:22) Rolle

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

(24 Feb '20, 14:21) Breck Carter

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

(24 Feb '20, 15:02) Breck Carter

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

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

(24 Feb '20, 15:21) Volker Barth

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

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

(24 Feb '20, 16:10) Breck Carter

More comments hidden

showing 5 of 16 show all flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

execute ×12
execute-immediate ×7

question asked: 21 Feb '20, 10:08

question was seen: 800 times

last updated: 24 Feb '20, 16:10

SAP SQL Anywhere

Remove quotes in field names with sql

Follow this question

Related questions