Search code examples
oracle11gpaddingtrim

Oracle SQL match string with left zeros padding

I have to find strings which can or cannot starts with zeros. For instance, if I have in my db the values '00563', '563', '43563', '0563', I have to find the rows '00563', '563', '0563'.

I've tryed this:

select distinct val
from table_one
where val = '00563' OR 
      val = ltrim('00563', '0');

but doesn't work. val is a CHAR(5 BYTE) column.

Can you help me?

EDIT: To be precise, if I execute the query:

select distinct val
from table_one;

the result is:

5034 
(null)
4dsfd
01005
03030
03075
05034

To me, 05034 and 5034 are the same value.

Solution

  • You need to make the column value and the literal value you're searching for match - explicitly making them the same data type (and length).

    An earlier version of this answer gave various permutations based on unknowns and assumptions, but summarising comments: your val column is defined as char(5) (so the values are space-padded, which will have to be taken into account), may have values that can't be converted to numbers, and the value (literal or variable) you're comparing against is a number. Based on all of that most of the variations aren't relevant to you, and you can do:

    select distinct val
from table_one
where lpad(trim(val), 5, '0') = lpad(to_char(563), 5, '0');

    The val from the table column is trimmed to remove whitespace (because it's char), then padded with zeros up to the maximum length instead. The number you're comparing against is converted to a string and also zero-padded to the same length. Those two strings can then be compared.

    With some sample data:

    create table table_one(val char(5));
insert into table_one
  select '00563' from dual
  union all select '563' from dual
  union all select '43563' from dual
  union all select '0563' from dual
  union all select '09999' from dual
  union all select '4dsfd' from dual;

select distinct val
from table_one
where lpad(trim(val), 5, '0') = lpad(to_char(563), 5, '0');

VAL  
-----
00563
0563 
563

    Applying a function to the table column value will prevent any index on that column being used, but that doesn't seem to be avoidable unless you can clean up the data in your table.