Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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

asked 21 Feb '20, 10:08

Rolle's gravatar image

Rolle
558495161
accept rate: 0%

edited 21 Feb '20, 14:50


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%20Carter's gravatar image

Breck Carter
32.5k5417271050
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
1

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

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

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

Question tags:

×12
×7

question asked: 21 Feb '20, 10:08

question was seen: 1,120 times

last updated: 24 Feb '20, 16:10