Hi,
I have a bunch of bad credit cards in my database and need a SQL Script to
find out which ones are actually bad. Does any of you have a credit card
validation sql script that I maybe able to use?
Thank you,
ShivaYou will need to contact the issuer to see if accounts have been
closed, but otherwise, you can do a quick check by Googling card
validation rouitnes. Certain four-digit gorups can only be in certain
places for each card type.|||Sorry Celko. Your answer did not help. I checked google before posting this
message here. There is an algorithm to check for valid credit card numbers.
The SQL script for this validation is what I am interested in.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1146936845.448785.24310@.j73g2000cwa.googlegroups.com...
> You will need to contact the issuer to see if accounts have been
> closed, but otherwise, you can do a quick check by Googling card
> validation rouitnes. Certain four-digit gorups can only be in certain
> places for each card type.
>|||HAHA - A helpful answer from Celko really is expecting a lot, you're
lucky he didn't just complain about your code (though you avoided this
by not posting any).
If you post the algorithm, and your table structures (or the entire DDL
ideally) then perhaps it can be translated into some SQL.|||Though I should add, you didn't give him anything to work with. The
best way to get useful answers is to post what might seem excessive
amounts of detail. the more the better, scrolling down takes no time,
but if you give everyone loads of info on how to create your problem,
then what you want the solution to be, it makes things really easy for
everyone else, and you'll almost certainly get a response.|||On Sat, 6 May 2006 10:20:52 -0700, "Shiva" <arbitsquare@.hotmail.com>
wrote:
in <emBUuFTcGHA.2068@.TK2MSFTNGP02.phx.gbl>
>Hi,
>I have a bunch of bad credit cards in my database and need a SQL Script to
>find out which ones are actually bad. Does any of you have a credit card
>validation sql script that I maybe able to use?
>Thank you,
>Shiva
>
I used the information here to create VBScript to validate credit card
numbers. You should be able to understand this and convert it to
working TSQL fairly easily.
http://www.sitepoint.com/article/ca...ation-class-php
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||Try this:
/*
* DROP PROC val_credit_card_numbers
*/
IF OBJECT_ID('val_credit_card_numbers') IS NOT NULL
BEGIN
DROP PROC val_credit_card_numbers
PRINT '<<< DROPPED PROC val_credit_card_numbers >>>'
END
go
create procedure val_credit_card_numbers
as
begin
/*
Note: The following table must be created and populated prior to
calling this procedure:
create table #credit_card_numbers(
id int not null,
card_no char(20) not null,
valid char(1) not null
)
*/
declare @.digit int
create table #checksum(
id int not null,
digit smallint not null,
value smallint not null
)
create table #checksum_totals(
id int not null,
value smallint not null
)
create unique clustered index i1 on #checksum_totals(id)
-- Assume numbers are invalid unless proven otherwise.
update #credit_card_numbers
set valid = 'N'
if @.@.error <> 0
begin
raiserror 99999 'Error setting #credit_card_numbers.valid to N'
return 1
end
select @.digit = 20
-- Starting from the right most digit of each card number (excluding
check digit), multiply each digit with 1 or 2
-- (starting from 1 and alternating between 1 and 2 until the left
most digit is reached.)
while @.digit > 0
begin
insert #checksum(
id,
digit,
value
)
select id,
@.digit,
convert(smallint, substring(card_no, @.digit, 1)) *
(((len(rtrim(card_no)) - @.digit) % 2) + 1)
from #credit_card_numbers
where len(rtrim(card_no)) > @.digit
and IsNumeric(card_no) = 1
if @.@.error <> 0
begin
raiserror 99999 'Error inserting into #checksum'
return 1
end
select @.digit = @.digit - 1
end
-- Where the checksum is 2 digits, add each of the digits together.
update #checksum
set value = (value / 10) + (value % 10)
where value >= 10
if @.@.error <> 0
begin
raiserror 99999 'Error updating #checksum (1).'
return 1
end
-- Get checksum totals for each card.
insert #checksum_totals(
id,
value
)
select id,
sum(value)
from #checksum
group by
id
if @.@.error <> 0
begin
raiserror 99999 'Error inserting into #checksum_totals.'
return 1
end
-- Do the Mod 10 calculation.
update #checksum_totals
set value = (10 - (value % 10)) % 10
if @.@.error <> 0
begin
raiserror 99999 'Error updating #checksum_totals.'
return 1
end
-- Compare the check digit of each card number with the calculated
checksum to determine whether it's valid.
update #credit_card_numbers
set valid = 'Y'
from #credit_card_numbers c,
#checksum_totals ct
where c.id = ct.id
and IsNumeric(c.card_no) = 1
and convert(smallint, substring(c.card_no, len(rtrim(c.card_no)), 1))
= ct.value
if @.@.error <> 0
begin
raiserror 99999 'Error setting #credit_card_numbers.valid to Y'
return 1
end
return 0
end
go
IF OBJECT_ID('val_credit_card_numbers') IS NOT NULL
BEGIN
PRINT '<<< CREATED PROC val_credit_card_numbers >>>'
GRANT EXECUTE ON val_credit_card_numbers TO PUBLIC
END
ELSE
PRINT '<<< FAILED CREATING PROC val_credit_card_numbers >>>'
go|||Thank you! This is exactly what I was looking for.
-Shiva
<kriskirk@.hotmail.com> wrote in message
news:1146969910.177407.67600@.y43g2000cwc.googlegroups.com...
> Try this:
> /*
> * DROP PROC val_credit_card_numbers
> */
> IF OBJECT_ID('val_credit_card_numbers') IS NOT NULL
> BEGIN
> DROP PROC val_credit_card_numbers
> PRINT '<<< DROPPED PROC val_credit_card_numbers >>>'
> END
> go
> create procedure val_credit_card_numbers
> as
> begin
> /*
> Note: The following table must be created and populated prior to
> calling this procedure:
> create table #credit_card_numbers(
> id int not null,
> card_no char(20) not null,
> valid char(1) not null
> )
> */
> declare @.digit int
> create table #checksum(
> id int not null,
> digit smallint not null,
> value smallint not null
> )
> create table #checksum_totals(
> id int not null,
> value smallint not null
> )
> create unique clustered index i1 on #checksum_totals(id)
> -- Assume numbers are invalid unless proven otherwise.
> update #credit_card_numbers
> set valid = 'N'
> if @.@.error <> 0
> begin
> raiserror 99999 'Error setting #credit_card_numbers.valid to N'
> return 1
> end
> select @.digit = 20
> -- Starting from the right most digit of each card number (excluding
> check digit), multiply each digit with 1 or 2
> -- (starting from 1 and alternating between 1 and 2 until the left
> most digit is reached.)
> while @.digit > 0
> begin
> insert #checksum(
> id,
> digit,
> value
> )
> select id,
> @.digit,
> convert(smallint, substring(card_no, @.digit, 1)) *
> (((len(rtrim(card_no)) - @.digit) % 2) + 1)
> from #credit_card_numbers
> where len(rtrim(card_no)) > @.digit
> and IsNumeric(card_no) = 1
> if @.@.error <> 0
> begin
> raiserror 99999 'Error inserting into #checksum'
> return 1
> end
> select @.digit = @.digit - 1
> end
> -- Where the checksum is 2 digits, add each of the digits together.
> update #checksum
> set value = (value / 10) + (value % 10)
> where value >= 10
> if @.@.error <> 0
> begin
> raiserror 99999 'Error updating #checksum (1).'
> return 1
> end
> -- Get checksum totals for each card.
> insert #checksum_totals(
> id,
> value
> )
> select id,
> sum(value)
> from #checksum
> group by
> id
> if @.@.error <> 0
> begin
> raiserror 99999 'Error inserting into #checksum_totals.'
> return 1
> end
> -- Do the Mod 10 calculation.
> update #checksum_totals
> set value = (10 - (value % 10)) % 10
> if @.@.error <> 0
> begin
> raiserror 99999 'Error updating #checksum_totals.'
> return 1
> end
> -- Compare the check digit of each card number with the calculated
> checksum to determine whether it's valid.
> update #credit_card_numbers
> set valid = 'Y'
> from #credit_card_numbers c,
> #checksum_totals ct
> where c.id = ct.id
> and IsNumeric(c.card_no) = 1
> and convert(smallint, substring(c.card_no, len(rtrim(c.card_no)), 1))
> = ct.value
> if @.@.error <> 0
> begin
> raiserror 99999 'Error setting #credit_card_numbers.valid to Y'
> return 1
> end
> return 0
> end
> go
> IF OBJECT_ID('val_credit_card_numbers') IS NOT NULL
> BEGIN
> PRINT '<<< CREATED PROC val_credit_card_numbers >>>'
> GRANT EXECUTE ON val_credit_card_numbers TO PUBLIC
> END
> ELSE
> PRINT '<<< FAILED CREATING PROC val_credit_card_numbers >>>'
> go
>
