Comparing numbers as string in oracle

Question

Hi all.

I have a comparison in Oracle for two numbers. 010 and 10. They are equal as long as numeric equality is concerned; however, I need to compare them as String. I tried to_char but it doesn't work.

Are there any other functions which would let me do exact comparison of numeric value as string?

------------To clarify everyone's doubt---------------------

I have three columns address1 address2 and address3 I want to compare ONLY the digits in the concatenation of all three. so for example if values are as follows: address1 = 01 park avenue address2 = 20 golden gate address3 = null

then I would like to compare data in table to see if any of the address's concatenated value comes out to be 0120

However right now it's equalizing 120 also with 0120 which I do not desire.

Data is extracted and concatenated, so not stored in a type of column. All I need is to ensure, that these numbers are compared "EXACTLY" and not as numbers.

Please suggest.

Cheers

Answer 1

Are the numbers stored as varchars in the db? If the numbers are stored in integer variables then 010 will be same as 10.

SELECT 010 FROM DUAL

will return 10. This means that once you have stored any number with leading zeros as an integer you lose the leading zeros. You can't get back what you have lost.

Maybe I have understood you wrongly, can you rephrase your question?

Answer 2

You don't really have an option here - you are either comparing strings - or numbers.

The "strings":
"10"
"010"
"0010"
"00010"

when converted to an Integer all = 10.

If you start with an integer 10, you have no way of knowing how many leading zeros the "string" version of it should have. So store them all as strings, or compare them as numbers - meaning "10" = "010" = "0010" = 10.

Ron

Answer 3

You have a string field:

select '010' str from dual

The following select will return 1 row:

select * from (select '010' str from dual) where str=10

The following select will return no rows:

select * from (select '010' str from dual) where str='10'

So even if the field is a string if you just write =10 in the where clause Oracle will compare them as numbers. If you write ='10' Oracle will compare them as strings.

Answer 4

Is this what you're after?

Set up some example data:

create table address as
select
    '01 park avenue' address1,
    '20 golden gate' address2,
    '30 test' address3
from
    dual;

insert into address
select
    '01 park avenue' address1,
    '20 golden gate' address2,
    null address3
from
    dual;

insert into address
select
    '01 park avenue' address1,
    '20 golden gate' address2,
    null address3
from
    dual;

commit;

Here's a query that will find 'duplicates' by ordering by the concatenated number string. We extract the numbers from the address using regexp_replace on the address concatenation.

select
    address1 || address2 || address3 address_concat,
    regexp_replace(address1 || address2 || address3, '[^[:digit:]]')
            address_numbers_only
from
    address
order by
    address_numbers_only;

If you're looking for matches to a specific address - try something like this:

select
    *
from
    address
where
    regexp_replace(address1 || address2 || address3, '[^[:digit:]]') = 
            regexp_replace(:v_address1 ||
                    :v_address2 || 
                    :v_address3, '[^[:digit:]]');

For example:

select
    *
from
    address
where
    regexp_replace(address1 || address2 || address3, '[^[:digit:]]') = 
            regexp_replace('01 park avenue' ||
                    '20 golden gate' || 
                    null, '[^[:digit:]]');

-- returns...

ADDRESS1        ADDRESS2        ADDRESS3
01 park avenue  20 golden gate  
01 park avenue  20 golden gate

Answer 5

The one and only correct way to check for exact matches would be

select whatever
from addresses
where address1 = '01'
and   address2 = '20'
and   address3 is null;

(Substituting bind variables or other columns for the hardcoded values to taste).

The fact that you are ignoring this obvious solution suggests you have some motive for comparing concatenated strings, which you have not yet explained.

Matching by concatenation is troublesome, as you are discovering. It works providing all elements are populated and of a fixed length. Once we allow nulls or variable length values we are doomed. None of the following ought to match on the basis of equality of elements, but lo! through the magic of concatenation they do:

SQL> select * from dual
  2  where 1||23 = 12||3
  3  /

D
-
X

SQL> select * from dual
  2  where 1||null||2 = 1||2||null
  3  /

D
-
X

SQL>
SQL> select * from dual
  2  where 123||null||null = 1||2||3
  3  /

D
-
X

SQL>

The workaround for this problem is to explicitly demarkate the elementa in the concatenated string. For instance if we separate the elements in taht last example with tildes we no longer get a match...

SQL> select * from dual
  2  where 123||'~'||null||'~'||null = 1||'~'||2||'~'||3
  3  /

no rows selected

SQL>