Oracle substr(), implicit conversion?

Question

I am facing an issue with Oracle SQLs substr() function.

It looks as though substr() acts beyond its stated purpose so that,

substr(some.field, 0, 7) <> '1598200'

where

some.field = '1598200, 123456'

Coming into play here,

select sum(t.amount)
from ivtransaction t
inner join ivpaymentreminders p on p.transactionid = t.transactionid
left join ivinvoice i on i.invoiceid = t.invoiceid
where substr(p.collectiveinvoiceno, 0, 7) = '1598200'

In p.collectiveinvoiceno, 4 records are expected to turn up:

rownum | p.collectiveinvoiceno
---------------------------------
1      | 1598200
2      | 1598200
3      | 1598200, 123456
4      | 1598200, 456789

But only rows 1 and 2 turn up and add into sum(t.amount).

• When tested on its own, substr(p.collectiveinvoiceno, 0, 7) extracts the correct values
• Converting left and right sides of the argument using to_char makes no difference

Is this an implicit conversion of some sort? If so, how to fix it?

Thankful for any ideas,

Ingrid

EDIT: As it turns out, the substr(field, 1, 7) comparison didn't catch 4/4 rows because the target string is at the back in rows 3 and 4, NOT leading. I was fooled because in the application, the target string is displayed as leading!! I'm a beginner so this was very helpful anyway, thanks for all your input. Ingrid

Answer 1

substr(p.collectiveinvoiceno, 0, 7)

It is a bad practice to use 0 as start position in SUBSTR, you should use 1. Although, documentation states:

If position is 0, then it is treated as 1.

There is no problem with the SUBSTR in the filter predicate, it will fetch all the 4 rows.

SQL> WITH DATA AS(
  2  SELECT '1598200' str FROM dual UNION ALL
  3  SELECT '1598200' FROM dual UNION ALL
  4  SELECT '1598200, 123456' FROM dual UNION ALL
  5  SELECT '1598200, 456789' FROM dual
  6  )
  7  SELECT str, substr(str, 1, 7) FROM DATA
  8  WHERE substr(str, 1, 7) = '1598200';

STR             SUBSTR(
--------------- -------
1598200         1598200
1598200         1598200
1598200, 123456 1598200
1598200, 456789 1598200

There is no need to use TO_CHAR as you already have string data type on both sides. So, there is no question about implicit data type conversion. Look at the explain plan:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 560839587

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     4 |    36 |     8   (0)| 00:00:01 |
|   1 |  VIEW            |      |     4 |    36 |     8   (0)| 00:00:01 |
|   2 |   UNION-ALL      |      |       |       |            |          |
|   3 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
|   6 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

13 rows selected.

But only rows 1 and 2 turn up and add into sum(t.amount)

It depends on the JOIN condition. The filer predicate is working fine.