I am trying to generate 15-digit creditcard value (varchar) for testing.
I thought I could simply use the RAND function and extract 15 characters
from the right of the decimal. However this doesn't work, I lose most of th
e
significant digits when I try to conver to a varchar.
So I thought I would generate 15 random numbers and concatenate them
together. However this doesn't work either. When I repeatedly run the
statement below, I get do not get any randomness at all, the same digit
repeats over and over again and will only occasionally change.
SELECT str(10*RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) ))
What don't I understand? Can anyone help me out with this?SELECT RIGHT(RTRIM(CONVERT(DECIMAL(18,17),
10.0*RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) ))),15)
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1E08F862-FFFF-4DD0-8EFA-4F714DBD125F@.microsoft.com...
>I am trying to generate 15-digit creditcard value (varchar) for testing.
> I thought I could simply use the RAND function and extract 15 characters
> from the right of the decimal. However this doesn't work, I lose most of
> the
> significant digits when I try to conver to a varchar.
> So I thought I would generate 15 random numbers and concatenate them
> together. However this doesn't work either. When I repeatedly run the
> statement below, I get do not get any randomness at all, the same digit
> repeats over and over again and will only occasionally change.
> SELECT str(10*RAND( (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) ))
> What don't I understand? Can anyone help me out with this?
>|||I thought that most credit card numbers were a fixed length of 16
digits, in grouping of 4 digits, with the first grouping being the
issuer and the rest of the number following some rules.
That means that your random numbers are going to fail (well, you might
hit a valid card number by chance every few billion rows). Look at
these websites for some help:
[url]http://www.omnipilot.com/Tip%20of%20the%20W
.1768.8848.lasso[/url]
http://www.analysisandsolutions.com...e/ccvs/ccvs.htm|||Dave,
I find CHEKSUM(NEWID()) a good seed for the RAND() function.
Also, keep in mind that RAND() returns a float in the range 0 through 1
inclusive.
With the above in mind, I'd use something like:
select right(cast(rand(checksum(newid())) as decimal(15, 15)), 15)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1E08F862-FFFF-4DD0-8EFA-4F714DBD125F@.microsoft.com...
>I am trying to generate 15-digit creditcard value (varchar) for testing.
> I thought I could simply use the RAND function and extract 15 characters
> from the right of the decimal. However this doesn't work, I lose most of
> the
> significant digits when I try to conver to a varchar.
> So I thought I would generate 15 random numbers and concatenate them
> together. However this doesn't work either. When I repeatedly run the
> statement below, I get do not get any randomness at all, the same digit
> repeats over and over again and will only occasionally change.
> SELECT str(10*RAND( (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) ))
> What don't I understand? Can anyone help me out with this?
>|||>I thought that most credit card numbers were a fixed length of 16
> digits, in grouping of 4 digits
American Express is 15.|||--CELKO-- wrote:
> I thought that most credit card numbers were a fixed length of 16
> digits, in grouping of 4 digits, with the first grouping being the
> issuer and the rest of the number following some rules.
> That means that your random numbers are going to fail (well, you might
> hit a valid card number by chance every few billion rows). Look at
> these websites for some help:
> [url]http://www.omnipilot.com/Tip%20of%20the%20W
.1768.8848.lasso[/url]
> http://www.analysisandsolutions.com...e/ccvs/ccvs.htm
Well, he might generate 15 digits and then calculate the last digit
using luhn, so it should at least pass that check.
On a curiosity side-not - anyone ever implemented luhn as a check
constraint? I'd love to see the beast.
Damien
