How to Calculate the Check Digit on a Columbia Gas Account Number

Introduction

This is a function that consumes a Columbia Gas account number of 11+ digits and returns the check digit.

Often times, when working with Columbia Gas utilities, they do not provide the full 12 digit account number, where the 12^th digit is the check digit. This is problematic when trying to join columns of account numbers from tables where some have 11 digits and others have 12.

Background

My first thought was they used the same algorithm as credit cards do, called the Luhn method. And it does, to an extent. The difference is that instead of summing the digits of the product of each even number and 2, you just use the product.

Using the Code

This is a SQL Server function that accepts varchar(12) and returns the check digit as char(1). Of course, this can be modified if you want to return the full 12 digit account number instead of just the check digit.

I called the function fn_COLUMBIA_CHECKDIGIT, but can be modified to meet your naming convention.

CREATE FUNCTION [IMPORT].[fn_COLUMBIA_CHECKDIGIT] (
    @ACCOUNT_NUMBER VARCHAR(12)
)
RETURNS CHAR(1)
AS
BEGIN
    SET @ACCOUNT_NUMBER = LEFT(LTRIM(RTRIM(@ACCOUNT_NUMBER)), 11)
    IF @ACCOUNT_NUMBER LIKE '%[^0-9]%' RETURN NULL
    IF LEN(@ACCOUNT_NUMBER) <> 11 RETURN NULL

    RETURN CAST(
            ((
            (CAST(SUBSTRING(@ACCOUNT_NUMBER, 1, 1) AS INT)) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 2 , 1) AS INT) * 2) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 3 , 1) AS INT)) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 4 , 1) AS INT) * 2) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 5 , 1) AS INT)) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 6 , 1) AS INT) * 2) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 7 , 1) AS INT)) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 8 , 1) AS INT) * 2) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 9 , 1) AS INT)) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 10 , 1) AS INT) * 2) + 
            (CAST(SUBSTRING(@ACCOUNT_NUMBER , 11 , 1) AS INT))
            * 9) % 10)
            AS CHAR(1))
END
GO
--test
SELECT  '12345678901' + IMPORT.fn_COLUMBIA_CHECKDIGIT('12345678901'), --123456789014
        '12345678921' + IMPORT.fn_COLUMBIA_CHECKDIGIT('12345678921')  --123456789218

History

• 19^th November, 2020: Initial version