Bank numbers check

I want to make a query that check in a table for bank numbers that are incorrect. This need to be done in sql and using modulo 97.

Thanks

Modulo operator in SQL server is % (which returns the remainder of an integer division).

So to get modulo 97 you do:

<somenumber> % 97

You might need to convert the datatype of your bank number from varchar to int or bigint to be able to perform this operation. If the bank number contains any non-number characters such as . or - then you must strip these out.

|||

Don't know if this is what you're after but I wrote this to validate ABA numbers:

public class CheckFedWireRoutingNumber
{

//Checks that the fed wire routing number (also known as an ABA number)
//is in the valid format. Does not verify that it exists, just that
//it's in the right format
static public bool IsABA(string abaNumber)
{
//Validate format of ABA number. This is taken fromhttp://www.brainjar.com/js/validation/
//andhttp://javascript.internet.com/forms/aba-routing-number-checksum.html
//andhttp://www.azcode.com/ABA/aba.htm
//function isABA(t)
//
//Here's a javascript version, taken from the first url, above
//{
// // Remove dashes if entered
// t = removeCharacters(t, '-');
//
// // All digits should be numeric (0 - 9)
// if (!isInteger(t))
// return false;
//
// // Run through each digit and calculate the total.
// n = 0;
// for (i = 0; i < t.length; i += 3)
// {
// n += parseInt(t.charAt(i), 10) * 3
// + parseInt(t.charAt(i + 1), 10) * 7
// + parseInt(t.charAt(i + 2), 10);
// }
// if (n != 0 && n % 10 == 0)
// return true;
// else
// return false;
//}

//remove dashes and spaces
abaNumber=abaNumber.Replace("-","").Replace(" ","");
if (!IsNumeric(abaNumber))
{
return false;
}

//Perform checksum
int n = 0;
try
{
for (int i = 0; i < abaNumber.Length; i += 3)
{
n += (Convert.ToInt32(abaNumber.Substring(i,1)) * 3) +
(Convert.ToInt32(abaNumber.Substring(i+1,1)) * 7) +
(Convert.ToInt32(abaNumber.Substring(i+2,1)));
}
}
catch (ArgumentOutOfRangeException)
{
//Caused by an ABA number that is not the right length
return false;
}
if (n != 0 && n % 10 == 0)
{
return true;
}
else
{
return false;
}
}

//Each character should be a valid number
static public bool IsNumeric(string input)
{
foreach(char c in input)
{
if(!char.IsNumber(c)) return false;
}
return true;
}

}

|||

Thanks dbland07666 but I need to do it in sql.

Johram how does this convert work? I now how to do that in vb.net code but not in sql. And another problem is that I only need to see the record where the bank number is incorrect. And I also don't may see the records where there is no bank number.

Thanks

|||

Could you give us an example of how a bank number looks like in your database? And what is the algorithm for verifying that it is valid? Is modulo 97 the only check you do? If so, what result do you expect in order to determine if it is valid? We need to know how your check is made in order to help you with the SQL code. Thanks!

|||

My bank numbers are only numbers.

BN = 72020290081: 97 - (modulo 97 van 720202900) = 97 - 16 =81

Thanks

Davidnyh

|||

Basically, what you need to do in your SQL is to split the number to get the "number part" and the "checksum part". Then you perform the modulo check and compare with the given checksum.

OK, so here's our first shot at it. Assuming table nameAccount and column nameNumber. This query will list all accounts with their calculated checksums, next to their given checksums.

SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account

Visually, we can now compare the calculated checksum with the given checksum and spot the deviations. But we need to do this in code as well.

If we only want the numbers that are valid, we can do something like:

SELECT NumberFROM AccountWHERECAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT) = 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97

Maybe you would like to list all accounts and have a column which says whether it is valid or not? Then we can do like this:

SELECT Number,'Yes'AS ValidFROM(SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account)AS T1WHERE GivenChecksum = CalculatedChecksumUNIONSELECT Number,'No'AS ValidFROM(SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account)AS T2WHERE GivenChecksum != CalculatedChecksumOR GivenChecksumISNULL

Good luck with the numbers! If you have any more questions, just let us know!

Please don't forget to mark this post as answer if it was useful to you. Thanks!

|||

I get this error: Argument data type decimal is invalid for argument 1 of substring function.

And if I try CAST(Number AS INT) I get this error: Arithmetic overflow error converting expression to data type int.

|||It works.Thanks|||It works. Thanks