How to ignore Null values at the end of a string in Oracle?

I have a column nse_credit_rating with datatype VARCHAR2(18). It contains values of form 189.00-250.00. But some records have null values at the end of the string i.e even if the data relevant to me is of length 13 (189.00-250.00) but length(nse_credit_rating) returns 17 as output i.e there are 4 null values at the end.

Main issue occurs when I have to use to_number on this column, it returns "ORA-1722 invalid number" since it contains null.

Is there a way I can get the sub string before the occurrence of NULL values? I even took the output of dump(nse_credit_rating).

SELECT dump(nse_credit_rating),nse_symbol 
FROM nse_security_master 
where nse_series='EQ'
  and nse_symbol like 'ARVIND' 
  and nse_series='EQ'

Output:

Typ=1 Len=17: 51,54,55,46,48,48,45,52,52,56,46,53,48,0,0,0,0

Here 0 is the ascii for NULL.

SELECT (nse_credit_rating),nse_symbol 
FROM nse_security_master 
where nse_series='EQ'
and nse_symbol like 'ARVIND' 
and nse_series='EQ'

Output:

367.00-448.50

I tried using ascii(substr(nse_credit_rating,-1,1)) but this only works when I know that only one character in the end would be Null. But there could be any number of characters having NULL.

Solution

Use rtrim() to remove characters from the end of a string:

SELECT rtrim(nse_credit_rating, chr(0)) as nse_credit_rating
      ,nse_symbol 
FROM nse_security_master 
where nse_series='EQ'
and nse_symbol like 'ARVIND' 
and nse_series='EQ'

This is covered in the Oracle SQL documentation. Find out more. Oracle also supports the standard SQL trim() function but that is slightly more verbose:

SELECT trim(trailing chr(0) from nse_credit_rating) as nse_credit_rating
       , nse_symbol 
FROM nse_security_master 
where nse_series='EQ'
and nse_symbol like 'ARVIND' 
and nse_series='EQ'

Can you tell me what chr(0) does here?

chr() is a function for turning ASCII numbers into characters. So chr(0) gives us ASCII null.