有没有办法在 SELECT * FROM 语句中修剪所有数据? [英] Is there a way to TRIM all data in a SELECT * FROM statement?

问题描述

我正在尝试使用以下语句从表中选择和修剪所有条目:

I am trying to select and trim all the entries from a table using the following statement:

SELECT TRIM(*) FROM TABLE

但是我得到一个错误.有没有办法返回所有选定的条目，以便在每个字符串的开头和结尾为空白字符修剪它们?

But I get an error. Is there a way to return all entries selected so they are trimmed for blank characters at the beginning and end of each string?

推荐答案

您需要手动指定每个字符串列:

You need to specify each string column by hand:

SELECT TRIM(col1),       --LTRIM(RTRIM(...)) If RDBMS is SQL Server
       TRIM(col2),
       TRIM(col3),
       TRIM(col4)
      -- ...
FROM table

你的提议还有一个问题.* 是表中每一列的占位符，因此修剪日期/十进制/空间数据...会出现问题.

There is another problem with your proposal. * is placeholder for each column in table so there will be problem with trimming date/decimal/spatial data ....

使用Oracle 18c 多态表函数(前提是代码只是PoC，还有很多改进的空间):

Using Oracle 18c Polymorphic Table Functions(provided code is just PoC, there is a space for a lot of improvements):

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) THEN
           tab.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<>小提琴演示

这篇关于有没有办法在 SELECT * FROM 语句中修剪所有数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！