0

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'

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
Fringo
  • 335
  • 1
  • 3
  • 17
  • 1
    `WHERE CAST(identity_number AS Int) in (123456,789123)` might be one option. However, if that identity_column ever has a non-numeric in it then this won't work. – JNevill Sep 28 '18 at 13:57
  • When I do that I get the following ERROR: Bad numeric input format '52e44fe8-a416-4ac1-88ac-821ee346b7cf' – Fringo Sep 28 '18 at 13:59
  • What database are you using? The query should run as-is in some RDBMS. – Salman A Sep 28 '18 at 14:00
  • Netezza-SQL database in Aginity – Fringo Sep 28 '18 at 14:02
  • 1
    @JNevill You do not need the `CASE` though... – Usagi Miyamoto Sep 28 '18 at 14:08
  • @UsagiMiyamoto yes... I just caught that. I wrote the case, then discovered the ltrim() second parameter... `WHERE ltrim(identity_number, '0') IN ('123456', '789123')` is more appropriate here. – JNevill Sep 28 '18 at 14:10
  • Whatever you do isn't going to be pretty since anything that does this is going to either transform the column values before comparison or use features of the column value to transform the match data - meaning no indexes will work and it'll have to process every row in the table. – Damien_The_Unbeliever Sep 28 '18 at 14:17
  • Why not just convert the values to numbers? – Gordon Linoff Sep 28 '18 at 14:28

2 Answers2

3

Try something like this:

SELECT *
FROM MYTABLE
WHERE TRIM(LEADING '0' FROM identity_number) IN ('123456', '789123')
Usagi Miyamoto
  • 6,196
  • 1
  • 19
  • 33
1

Please try this. But this might get slow in large table. 

select * from MYTABLE
where TRIM(LEADING '0' FROM identity_number) in ('123456', '789123')
nitzien
  • 1,117
  • 9
  • 23