2

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.

arjun gaur
  • 518
  • 1
  • 9
  • 25

2 Answers2

4

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.

Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219
APC
  • 144,005
  • 19
  • 170
  • 281
-2

The simple way is to use the trim function. Detailed info at below link https://docs.oracle.com/javadb/10.8.3.0/ref/rreftrimfunc.html

or use the rtrim directly.

Krunal Barot
  • 914
  • 6
  • 17
  • 1
    it is a common function.. the aim here was to point in right direction.. but added another link for this specific case. – Krunal Barot Apr 15 '19 at 13:00
  • 1
    Answers which just link to other places are not good answers. A good answer stands on its own, without the need for links. (Although links are a fine way of supplementing an answer or providing more information.) – APC Apr 15 '19 at 13:52
  • using rtrim directly doesn't solve the problem ,but as @APC mentioned rtrim(nse_credit_rating,chr(0)) gets the result. – arjun gaur Apr 15 '19 at 14:03
  • I got your point of having an answer to stand on its own and agree to it. But I generally believe in " teach a man to fish approach" . Explore more, try out and learn few extra things out of it. It is just a different viewpoint. :) – Krunal Barot Apr 18 '19 at 13:46
  • 1
    *" It is just a different viewpoint."* Actually it isn't. StackOverflow has well-established conventions regarding what is and what isn't a good answer. Link only answers are definitely Not Good Answers. Please read [this Meta response to understand why](https://meta.stackexchange.com/a/8259/133829). – APC Apr 18 '19 at 14:54
  • Great APC.. thanks for the info of meta. Will surely keep this in mind. :) – Krunal Barot Apr 19 '19 at 15:09