您需要手动指定每个字符串列：
SELECt TRIm(col1), --LTRIm(RTRIm(...)) If RDBMS is SQL Server TRIm(col2), TRIm(col3), TRIm(col4) -- ...FROM table
您的建议还有另一个问题。
*是表格中每列的占位符，因此修剪会有问题
date/decimal/spatial data ...。
附录
使用
Oracle 18c Polymorphic Table Functions（提供的代码只是PoC，还有很多改进的空间）：
CREATE TABLE tab(id INT, d DATE, v1 VARCHAr2(100), v2 VARCHAr2(100), v3 VARCHAr2(100) );INSERT INTO tab(id, d,v1, v2, v3) VALUES (1, SYSDATE, ' aaaa ', ' b ', ' c');INSERT INTO tab(id, d,v1, v2, v3)VALUES (2, SYSDATE+1, ' afasd', ' ', ' d');COMMIT;SELECt * FROM tab;-- Output.----.-----------.-----------.-----------.-----.| ID | D | V1 | V2 | V3 |:----+-----------+-----------+-----------+-----:| 1 | 02-MAR-18 | aaaa | b | c |:----+-----------+-----------+-----------+-----:| 2 | 03-MAR-18 | afasd || d |'----'-----------'-----------'-----------'-----'
和表功能：
CREATE OR REPLACe PACKAGE ptf AS FUNCTION describe(tab IN OUT dbms_tf.table_t)RETURN dbms_tf.describe_t; PROCEDURE FETCH_ROWS;END ptf; /CREATE OR REPLACE PACKAGE BODY ptf AS FUNCTION describe(tab IN OUT dbms_tf.table_t) RETURN dbms_tf.describe_t AS new_cols DBMS_TF.COLUMNS_NEW_T; BEGIN FOR i IN 1 .. tab.column.count LOOP IF tab.column(i).description.type IN ( dbms_tf.type_varchar2) THENtab.column(i).pass_through:=FALSE;tab.column(i).for_read:= TRUE;NEW_COLS(i) := DBMS_TF.COLUMN_metaDATA_T(name=> tab.column(i).description.name, type => tab.column(i).description.type); END IF; END LOOP; RETURN DBMS_TF.describe_t(new_columns=>new_cols, row_replication=>true); END; PROCEDURE FETCH_ROWS AS inp_rs DBMS_TF.row_set_t; out_rs DBMS_TF.row_set_t; rows PLS_INTEGER; BEGIN DBMS_TF.get_row_set(inp_rs, rows); FOR c IN 1 .. inp_rs.count() LOOP FOR r IN 1 .. rows LOOP out_rs(c).tab_varchar2(r) := TRIm(inp_rs(c).tab_varchar2(r)); END LOOP; END LOOP; DBMS_TF.put_row_set(out_rs, replication_factor => 1); END;END ptf;
最后的电话：
CREATE OR REPLACE FUNCTION trim_col(tab TABLE)RETURN TABLE pipelined row polymorphic USING ptf;SELECT *FROM trim_col(tab); -- passing table as table function argument.----.-----------.-------.-----.----.| ID | D | V1 | V2 | V3 |:----+-----------+-------+-----+----:| 1 | 02-MAR-18 | aaaa | b | c |:----+-----------+-------+-----+----:| 2 | 03-MAR-18 | afasd | - | d |'----'-----------'-------'-----'----'
db <>fiddle演示
