oracle sql to remove semi column from the end or individually-CodePudding

I am getting an output of one table as

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

I want to tweak the above column such as ; at the end is removed and if only ";" is given it should be removed like

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

CodePudding user response：

Use RTRIM():

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

You could also use REGEXP_REPLACE here:

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

CodePudding user response：

Right-trim it.

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>