I have a table (let's call it MYTABLE) and within MYTABLE I have a column identity_number, but this number is stored as a string (that's the way it is, I can not change this, because I am not the owner of the data). However, sometimes this number has a leading zero in the table and sometimes not (for this accounts the same, I can not change this). So for example VARCHAR '123456' occurs in the column identity_number, but VARCHAR '0789123' does also occur in the column. Now I want to do a select * from on my table in which I do not give the leading zero to my argument in the where-clause, but I stall want it to return both records. How can I achieve this?
So:
select * from MYTABLE
where identity_number in ('123456', '789123')
should return both records: '123456' and '0789123'
Try something like this:
SELECT *
FROM MYTABLE
WHERE TRIM(LEADING '0' FROM identity_number) IN ('123456', '789123')