I am trying to compare two columns in two different tables in a database. One column is of type VARCHAR2(4 CHAR) and the other is of type DECIMAL. Although I am pulling these columns into JAVA as a string to do the comparison, I am running into this issue:
Elements in column (decimal) are: 123, 456, 789
Elements of compared column (varchar) are: 0123, 0456, 0789
So I want to know if there is a way that I can remove this prefixed 0 as I am pulling it from the database (like some sort of TRIM0()) such that elements from both columns would be the same?
I am trying to avoid handling this column in the JAVA itself to improve performance.
(like some sort of TRIM0()) such that elements from both columns would be the same?
Your requirement is about data type conversion, not string manipulation.
Elements might look similar, but it is the data type that matters. '123' and 123 are not the same. They look similar, but they are different data types.
Do not use LTRIM as it would return you a STRING, you will have to convert it to NUMBER again to avoid implicit data type conversion. It is pointless to return a string, and then use TO_NUMBER again to convert it to number.
The correct and efficient way is to use TO_NUMBER. It would convert it into NUMBER in just one step.
For example,
SQL> SELECT to_number('00123') num FROM dual;
NUM
----------
123
SQL> SELECT * FROM dual WHERE to_number('00123') = 123;
D
-
X
On a side note, if you are sure that always need a NUMBER from the column to process, then you must fix it at design level. Storing numbers as character type is a design flaw, unless there is a specific business need.