credit card in range of cards

[alex]

Hi ,

we have CreditCardRange table

create table CreditCardRange(
ccr_CudtomerID int,
ccr_CardRangeFrom char(16),
ccr_CardRangeTo char(16)
)

table Orders
ord_id int
ord_CudtomerID int,
ord_CardNo char(16)


Example values in table CreditCardRange
1 , 5000 0000 2000 1234 , 5000 0030 3000 1356

Example values in table Orders
1, 1, 5000 0010 2000 2333

Does sql provide any magic finction to help in following select

select T1.*
from Orders T1
join CreditCardRange T2
on T2.ccr_CudtomerID = t1.ord_CudtomerID
and
SQLMagicFunction(T1.ord_CardNo)
between
SQLMagicFunction(T2.ccr_CardRangeFrom )
and
SQLMagicFunction(T2.ccr_CardRangeTo )

[alex]

any other way then ..

select T1.*
from Orders T1
join CreditCardRange T2
on T2.ccr_CudtomerID = t1.ord_CudtomerID
and
cast(T1.ord_CardNo as bigint)
between
cast(T2.ccr_CardRangeFrom as bigint)
and
cast(T2.ccr_CardRangeTo as bigint)

[nosepicker]

How about:

SQLMagicFunction = CONVERT(bigint, REPLACE(ord_CardNo, ' ', ''))

(can use CAST also ...)