Saturday, April 04, 2015

Remove the Trailing Character From a String Using SQL

We can using SQL function substr to remove the last character from a string. For example, the following query shows there is a extra comma at the end of the string. Please also see Remove Character at Specific Location in String.

SQL> select * from tbl_test;

     ID VAL
------- --------------------------------
      1 Hello,
The following query will remove it. Substr takes three parameters. The first parameter is the string to process, the second is the starting position and the third is the length of the substring to extract. Here, we define the length to be the total length of the original string minus one.
SQL>  select id, substr(val, 1, length(val)-1) val from tbl_test;

     ID VAL
------- --------------------------------
      1 Hello

No comments: