I am not sure whether it is possible or not, I have one DB table which is having fields refNumber, Some of this fields values contains two leading zeros, following is example.

Now I am trying to write a query which can select from this table with our without leading zeros (Only two not less or greater than two).Here is an example, for select refNumber=123 OR refNumber=00123 should return result 10001 and for refNumber=00456 OR refNumber=456 should return result of 10002. I can not use like operator because in that case other records might also be return. Is it possible through the query? if not what would be the right way to select such records? I am avoiding looping the all rows in my application.

CodePudding user response：

You need to apply TRIM function on both - column and the value you want to filter by:

SELECT * FROM MyTable
WHERE TRIM(LEADING '0' FROM refNumber) = TRIM(LEADING '0' FROM '00123') -- here you put your desired ref number

CodePudding user response：

Use trim()

    Select * from table where trim(refnumber) IN ('123','456')

Or replace() whichever supported

 Select * from table where
 replace(refnumber, '0','') IN 
('123','456')

CodePudding user response：

While the currently accepted answer would work, be aware that at best it would cause Db2 to do a full index scan and at worst could result in a full table scan.

Not a particularly efficient way to return 1 or 2 records out of perhaps millions. This happens anytime you use an expression over a table column in the WHERE clause.

If you know there's only ever going to be 5 digits or less , a better solution would be something that does the following:

SELECT * FROM MyTable
WHERE refNumber in ('00123','123') 

That assumes you can build the two possibilities outside the query.

If you really want to have the query deal with the two possibilities.. SELECT * FROM MyTable WHERE refNumber in (LPAD(:value,5,'0'),LTRIM(:value, '0'))

If '00123' or '123' is pass in as value, the above query would find records with '00123' or '123' in refNumber.

And assuming you have an index on refNumber, do so quickly and efficiently.

If there could be an unknown number of lead zeros, then you are stuck with

SELECT * FROM MyTable
WHERE LTRIM(refNumber,'0') = LTRIM(:value, '0')

However, if you platform/version of Db2 supports indexes over an expression you'd want to create one for efficiency's sake

create index myidx
  on MyTable (LTRIM('0' from refNumber))