CODEWITHSUNDEEP
plsqloracle11g
user2924127
user2924127

Reputation: 6240

Converting from number to string not working as expected

I have a table called my_table:

| id (number) | some_data ( varchar2(3) ) |

I then query this table using :P_DATA = 'BOB':

SELECT * FROM my_table 
where upper(some_data) = upper(CAST(:P_DATA AS VARCHAR(3) ) ; 
// SELECT * FROM my_table where upper(some_data) = upper(CAST('BOB' AS VARCHAR(3) )

This works great, but when the bind variable is a number 010 for instance, it returns nothing:

SELECT * FROM my_table 
where upper(some_data) = upper(CAST(:P_DATA AS VARCHAR(3) ) ; 
// SELECT * FROM my_table where upper(some_data) = upper(CAST(010 AS VARCHAR(3) )

If I put single quotes around the 010 then it returns data:

SELECT * FROM my_table 
where upper(some_data) = upper(CAST(:P_DATA AS VARCHAR(3) ) ; 
// SELECT * FROM my_table where upper(some_data) = upper(CAST('010' AS VARCHAR(3) )

How can I have this work for all these scenarios, even when the bind variable is a number?

Upvotes: 0

Views: 100

Answers (1)

APC
APC

Reputation: 146239

The thing is, numbers don't have significant leading zeroes: 010 = 0010. Whereas '010' is a string and its leading zeroes are significant: '010' != '0010'.

So when you pass a numeric value to :p_data it has no significant leading zeroes, literally they are stripped off by dint of it being a number. So when you cast it to a string you get '10' which obviously is different from '010'. This is easy to demonstrate:

SQL> select * from dual
  2  where '010' = cast(010 as varchar2(3))
  3  /

no rows selected

SQL> select * from dual
  2  where '10' = cast(010 as varchar2(3))
  3  /

D
-
X

SQL>

As for how to work around this? Oracle database is strongly datatyped. Storing different datatypes in one column is widely regarded as bad practice because it always creates problems for the reading processes. But we'll discard the possibility of changing this approach. So other things you could do:

  1. Add a datatype column which tells readers how to cast the stored value.
  2. Ensure that values which are intended to be numbers are stored as numbers i.e. no leading zeroes. This might be messy (other parts of the application may explicitly handle leading zeroes and so will break) and it will rquire constant vigilance.

There are no easy answers.

Upvotes: 2

Related Questions