Matching a field with a variable number of leading zeros in a SQL Server table

Question

I have a situation where I have an input value that may or may not have leading zeros. I need to map this to a field / row in a SQL Server table. The value of a field in a SQL Server database may or may not have null values.

So, I could:

• incoming = 5042800138
  and the value in db can be any of 5042800138, 05042800138, 005042800138, 0005042800138

• or incoming can be 005042800138
  and the value in db can be any of 5042800138, 05042800138, 005042800138, 0005042800138

The solution I came across was to remove the leading zeros (always) from the incoming data and use SQL, as in the following example:

-- this simulates the incoming value to check
-- i strip out the leading zeroes.
declare @tryUPC as varchar(40)
set @tryUPC = '5042800138'

-- try to find it in the database and ignore leading zeroes
select prod_uid, prod_partno, prod_upc
from products as p
where (prod_upc = @tryUPC) or 
   (
   len(prod_upc) > len(@tryUPC)
   and right(prod_upc, len(@tryUPC)) = @tryUPC
   and stuff(prod_upc, 1, len(prod_upc) - len(@tryUPC), '0') = prod_upc
   )

. : - ? SQL Server ? SQL Server 2005.

tia,

Answer 1

, / INT, , - :

WHERE prod_upc IN (@tryUPC, '0' + @tryUPC, '00' + @tryUPC, '000' + @tryUPC [...])

, , , , .

, , . INT ( INT ), , .

Answer 2

( , ): "actualUPC", , . :

"" 12 , ,

 right( '000000000000' + originalColumn, 12 )

, , .

, , .

, , , .

(, , , ssn ..) . , 01033.

Answer 3

1) , - , , BIGINT
2)
3) , !

OP:

, . , , . upc . . , ARE - , , . -

, REVERSE() , . :

WHERE Column1Reverse Like REVERSE('1234567')+'%' --can use the persistent computed column index

( ) , :

ALTER TABLE YourTable ADD ReversedYourString AS REVERSE (YourString) PERSISTED

CREATE NONCLUSTERED INDEX IX_YourTable_ReversedYourString 
ON YourTable (ReversedYourString)