BETWEEN and Alphanumeric - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

BETWEEN and Alphanumeric

Author

Topic

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-25 : 00:57:14

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 01:33:34

is your format consistent for voucher code ie always two alphabets followed by numeric data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-25 : 03:02:23

Visakh, yes it is always consistent!

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 03:25:27

If VoucherNumber is a numeric / integer value between 5258 and 26258 then you can do:


sum(case when vouchernumber 
    between CONVERT(bigint, SUBSTRING('AB00000000005258', 3, 14))
        and CONVERT(bigint, SUBSTRING('AB00000000026258', 3, 14))
    then 1 else 0 end) as vcnt

EDIT: Changed from INT to BIGINT in case you have 'AB9...' - that's a lot of vouchers!

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-25 : 04:00:19

Thanks Kristen, will give that a go.

If we had AB9..... we would be out of business!!!

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-25 : 04:43:49

Kristen, I just get an error message saying that the conversion has failed! Any ideas? Full code below (with the actual voucher codes)

Set datefirst 1
Select
datepart(YEAR,s.saledate) as year,
datepart(wk,s.saledate) as week,
s.branchcode,
sum(coalesce(s.fcextendednetamount-s.fcextendedtaxamount,0)) as salesdollars,
sum(coalesce(s.quantity,0)) as saleunits,
sum(coalesce(s.grossprofitalternateGP,0)) as GPdollars,
sum(coalesce(s.fcextendednetamount-s.fcextendedtaxamount-s.grossprofitalternateGP,0)) as salescost

FROM EEE..vwsaleline_gp s

left join (select reference1,
sum(case when vouchernumber
between CONVERT(bigint, SUBSTRING('KC000000000000800011', 3, 18))
and CONVERT(bigint, SUBSTRING('KC000000000001667401', 3, 18))
then 1 else 0 end) as vcnt
from eee..vouchertransaction
group by reference1)v
on v.reference1 = s.reference1
where s.saledate >= '08/25/2011'
and (v.vcnt=0 or v.vcnt is null)

group by datepart(year,s.saledate),datepart(wk,s.saledate),s.branchcode
order by datepart(year,s.saledate),datepart(wk,s.saledate),s.branchcode

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 04:54:04

you can do like


select
sum(case when Alphapart = 'AB' and NumPart BETWEEN 5258 AND 26258 then 1 else 0 end) as vcnt,
....
from'(
select *,left(vouchernumber ,patindex('%[0-9]%',vouchernumber )-1) as Alphapart,
cast(substring(vouchernumber ,patindex('%[0-9]%',vouchernumber ),len(vouchernumber)) as bigint) as Numpart
from table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 04:56:24

"If we had AB9..... we would be out of business!!!"

I had that thought too!

"I just get an error message saying that the conversion has failed"

What datatype is vouchernumber column?

Given you are trying hard-wired values try:


sum(case when vouchernumber 
between 800011 and 1667401
then 1 else 0 end) as vcnt

to see if that works. Maybe vouchernumber datatype cannot be compared against BIGINT - in which case you can use INT isntead, but it will blow up if your voucher code exceeds the numeric limit for an INT

Alternatively cast VoucherNumber to be BIGINT too:


sum(case when CONVERT(bigint, vouchernumber) ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources