BETWEEN and Alphnumeric

[barnettjacob]

I've got a series of voucher codes that are alphanumeric and I need to establish whether a transaction is associated with the voucher code.

There are 21,000 codes which are sequential e.g. AB00000000005258 to AB00000000026258 but I'm presuming that becuase they are alphanumeric I can't do the following:

sum(case when vouchernumber between AB00000000005258 and AB00000000026258 then 1 else 0 end) as vcnt

Can anybody help?
Thanks
Jacob

 

[bborissov]

SUM(case when vouchernumber between [COLOR=red][b]'[/b][/color]AB00000000005258[COLOR=red][b]'[/b][/color] and [COLOR=red][b]'[/b][/color]AB00000000026258[COLOR=red][b]'[/b][/color]  then 1 else 0 end)

But I'm not so sure that this BETWEEN will work correctly.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[barnettjacob]

Thanks Boris, omitting the ' was a typo but can confirm you are right, it doesn't work!

 

[IanWaterman]

I would have thought this would work

sum(case when vouchernumber >= 'AB00000000005258' and vouchernumber <= AB00000000026258  then 1 else 0 end) as vcnt

Ian

 

[barnettjacob]

Thanks Ian, will give that a try!
Jacob

 

[bborissov]

What if you convert it like this (NOT TESTED!!!!)

SUM(CASE WHEN LEFT(vouchernumber,2) = 'AB' AND
              CAST(SUBSTRING(vouchernumber, 3, 8000) As int) BETWEEN 5258 AND 26258)
              THEN 1
         ELSE 0 END)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.