question

jhowe avatar image
jhowe asked

generate test card numbers

Hi all how do i create random 16 digit numbers in SQL server? I would like these numbers to start with a 1, thanks for your help!
sql-server-2008sqlsql-server-2008-r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
It depends quite how random and how unique you want them... Select ABS(CHECKSUM(NEWID())) % will give you a random number up to the value of `INT` Note however, its not guaranteed to be unique or truly random. If you need a hand prefixing it with a 1 come back again. [edit - full solution] DECLARE @var bigint SELECT @var = 100000000000000 + (CAST(ABS(CHECKSUM(NEWID())) % 10000000 AS bigint) * CAST(ABS(CHECKSUM(NEWID())) % 10000000 AS bigint)) PRINT @var print LEN(@var) GO 20 should always give a 15 digit number starting with a 1
9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You can use the expression provided by @Fatherjack in a CROSSAPPLY in the UPDATE statement. See code below.. This will generate random value for each row of your table. UPDATE T SET YourField = R.RandomValue FROM YourTable T CROSS APPLY (SELECT 100000000000000 + (CAST(ABS(CHECKSUM(NEWID())) % 10000000 AS bigint) * CAST(ABS(CHECKSUM(NEWID())) % 10000000 AS bigint)) AS RandomValue) R
4 Likes 4 ·
If this is the answer to your problem then please click the tick next to the answer so that others can see how to solve this issue if they have a similar problem.
1 Like 1 ·
let me clarify... i have generated 10000 test customers and i would just like to apply a test card number to each of them, in the format 1 and then any 15 digit number after the 1...
0 Likes 0 ·
Yes, I understood that. Can you see from my example how you can use it to make the data that you require.
0 Likes 0 ·
ok yes i can see how that creates random numbers, how do i set it to only give me 15 digits and prefix a one at the start?
0 Likes 0 ·
Show more comments
cybarcom avatar image
cybarcom answered
SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function. It returns a random float value between 0 and 1. We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows: SELECT RAND() AS [RandomNumber] For more example please click :[URL][1] Thanks [1]: http://cybarlab.blogspot.com/2013/02/random-number-in-sql.html
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@cybarcom, SQL Server has the RAND() function. Unfortunately, this function is evaluated only once per query, so you cannot get random number for each row. Therefore the workaround with CHECKSUM() of NEWID() which generates unique GUID for every row processed if it is part of the select statement.
0 Likes 0 ·
And it doesn't actually generate a value between 0 and 1. It generates values >=0 and < 1. Ironically, it requires a random seed to return more than 1 random number if used in a set-based SELECT.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.