sqloraclebi-publisher

Remove semicolon from the end or individually

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
Solution

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