Converting from number to string not working as expected

Question

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?

Just always pass your bind variable in as a string. Then no need to cast it. — Boneist
– Boneist, Commented Jul 27, 2017 at 17:54
010 is the number 10, so when cast to string it is '10', not '010'. — trincot
– trincot, Commented Jul 27, 2017 at 17:59
@trincot is there a way to make sure the leading 0 is there? — user2924127
– user2924127, Commented Jul 27, 2017 at 18:02
Don't pass a number, but a string. A number type does not have the information that there is a prefixed zero, so you already lost it before passing the variable to the query. — trincot
– trincot, Commented Jul 27, 2017 at 18:05
Just so you realize if the application passes the number, you are already too late. There is no way you can know there originally was a prefixed zero. It was already removed by the application when it decided to convert the user's input to number. If you have control over the application, then replace the code responsible for that. — trincot
– trincot, Commented Jul 27, 2017 at 19:03

APC · Accepted Answer · 2017-07-28 05:34:09Z

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:

Add a datatype column which tells readers how to cast the stored value.
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.

Collectives™ on Stack Overflow

Converting from number to string not working as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related