Creating MOD10V05 BPay Reference Numbers Using T-SQL


BPAY is Australia’s most widely used bill payment service which enables bill payers to transfer funds electronically from their bank accounts to billers. It is offered as a payment channel by over 170 financial institutions through their phone and Internet banking services.

Since BPAY’s launch in 1997 electronic bill payment in Australia has grown at a phenomenal rate. BPAY can be used to pay over 19,000 different bills Australia wide and each month BPAY processes over 18 million individual payments.

Over the last couple of years there has been a substantial increase in the number of people who have access to internet banking which has resulted in a large increase in the number of people using BPAY. Now 72% of the Australian bill paying population use BPAY* with 87% of BPAY payments being made via the internet.

MSDN Gallery:

*source: TNS Research Nov 2009


DECLARE @TestID BIGINT = 120319 
declare @TestIDLength TINYINT = 1 
declare @returnedVal int = 0 
declare @inputVal varchar(50) = @TestID
declare @odd bit = 0 
declare @sum int 
declare @numberLeft int 

WHILE @TestIDLength <= LEN(@inputVal) 
        SET @sum =  CAST(SUBSTRING(@inputval,@TestIDLength,1) AS TINYINT) 
          * @TestIDLength 
        SET @returnedVal = @returnedVal + @sum 
        SET @TestIDLength = @TestIDLength + 1 
SET @numberLeft = @returnedVal % 10 

  + CAST(@numberLeft AS CHAR(1)) AS BIGINT) AS BPayRefNumber 

About Mr. Wharty's Ramblings
Jeff holds a Master’s Degree in Systems Development (.NET Stream) and a Master’s Degree in Database Design and Management (SQL Server). Jeff also holds MCPD, MCITP, MCDBA, MCSD and MCSE certifications.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: