从末尾或单独删除分号

Question

我得到一个表的输出

FILE_NUM                                SNO
Read;write;Listen;                      1
Listen;                                 2
;                                       3
Write;READ;                             4

我想调整上面的列，如;最后被删除，如果只有";"它应该像

一样被删除

FILE_NUM                            SNO
Read;write;Listen                   1
Listen                              2
3
Write;READ                          4

Answer 1

使用RTRIM():

SELECT RTRIM(FILE_NUM, ';') AS FILE_NUM, SNO
FROM yourTable
ORDER BY SNO;

您也可以在这里使用REGEXP_REPLACE:

SELECT REGEXP_REPLACE(FILE_NUM, ';$', '') AS FILE_NUM, SNO
FROM yourTable
ORDER BY SNO;

Answer 2

右修

SQL> with test (file_num) as
2    (select 'read;write;listen;' from dual union all
3     select 'listen;'            from dual union all
4     select ';'                  from dual union all
5     select 'write;read;'        from dual
6    )
7  select file_num,
8    rtrim(file_num, ';') result
9  from test;
FILE_NUM           RESULT
------------------ ------------------
read;write;listen; read;write;listen
listen;            listen
;
write;read;        write;read
SQL>