Search code examples
sqloraclesubstrto-char

SQL: using substr along with to_char

I am using Oracle SQL and want to know how the 2 queries below return the exact same thing sometimes and other times a different result.

select substr(to_char(min_code + 10, '099'),1,3)
from x
where a = b;


select substr(min_code + 10,1,2)
from x
where a = b;

The 1st query has the substr length set to 3, whereas the 2nd query has the substr length set to 2. However, when min_code is equal to 151 both queries will return 16. How is this possible?

I realise it must be to do with how substr is calculating the length when using to_char but it is my understanding that the the 2nd arguement (1 in these cases) is where the substring is to begin and the 3rd arguement is the length of the substring. Yet the 1st query still returns 16 instead of 161 as I would have imagined.

Then, when I test it with a record where min_code will equal 051 the 1st query will return 06, whereas the 2nd query will return 61. I understand how the 2nd query gets 61 as it must drop the leading 0 when performing the arithmetic operation BUT how on earth does the 1st query (with to_char function) return 06. I would expect 061 as length is 3.

Solution

  • Just putting your tests in a single query and removing the +10 (the issue is not there), this is what you have:

    SQL> with testCases(n) as ( select 151 from dual union select 51 from dual)
  2  select n,
  3         substr(to_char( n, '099'),1,3) as substr_3,
  4         substr(n,1,2) as substr_3
  5  from testCases;

         N SUBSTR_3     SUBSTR_3
---------- ------------ --------
        51  05          51
       151  15          15

    I believe what makes you expect a different result is what is to_char supposed to do; to clarify, have a look at the result of the following:

    SQL> with testCases(n) as ( select 151 from dual union select 51 from dual)
  2  select n,
  3         '|' || to_char( n, '099') || '|' as to_char
  4  from testCases;

         N TO_CHA
---------- ------
        51 | 051|
       151 | 151|

    Here you see that to_char added a leading space to the resulting strings; this makes your substring logic fail, giving you the unexpected result that you are having. This behaviour is clearly explained here:

    The extra leading space is for the potential minus sign. To remove the space you can use FM in the format

    In fact, if you edit the format mask, you have 

    SQL> with testCases(n) as ( select 151 from dual union select 51 from dual)
  2  select n,
  3         '|' || to_char( n, 'FM099') || '|' as to_char
  4  from testCases;

         N TO_CHA
---------- ------
        51 |051|
       151 |151|

    and your test cases become:

    SQL> with testCases(n) as ( select 151 from dual union select 51 from dual)
  2  select n,
  3         substr(to_char( n, 'FM099'),1,3) as substr_3,
  4         substr(n,1,2) as substr_2
  5  from testCases ;

         N SUBSTR_3     SUBSTR_2
---------- ------------ --------
        51 051          51
       151 151          15