CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2data-conversion
rexroxm
rexroxm

Reputation: 878

Convert x digit random number to varchar in SQL Server

I am creating a 14 digit random number in SQL Server 2008 R2 using the following query:

select FLOOR(RAND() * POWER(CAST(10 as BIGINT), 14))

Now I need to convert this number into varchar for which I use this query:

select 
    convert(varchar(50), FLOOR(RAND() * POWER(CAST(10 as BIGINT), 14)))

It convert the number but in 6.74151e+013 form.

So I tried this:

select 
    convert(varchar(50), FLOOR(RAND() * POWER(CAST(10 as BIGINT), 5))) +
    convert(varchar(50), FLOOR(RAND() * POWER(CAST(10 as BIGINT), 5))) + 
    convert(varchar(50), FLOOR(RAND() * POWER(CAST(10 as BIGINT), 4)))

It does create a 14 digit varchar but I feel there can be a shorter way to do so. I have tried various other ways too but they return the answer in the 6.74151e+013 format which I cannot use.

Please help.

EDIT: If there is another way of creating a 14 digit random numeric string directly(i.e. without using rand()) then do tell that also.

Upvotes: 1

Views: 3309

Answers (4)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

I'd use CRYPT_GEN_RANDOM, because it allows to specify how many random bytes you need.

Then, since you need decimal digits, not hexadecimal, cast result to bigint; then convert it to varchar and take last 14 digits:

SELECT RIGHT(CAST(CAST(CRYPT_GEN_RANDOM(8) AS bigint) AS varchar(50)), 14)

This can be simplified to:

SELECT RIGHT(CAST(CRYPT_GEN_RANDOM(8) AS bigint), 14)

because RIGHT converts bigint into varchar implicitly.

Upvotes: 1

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Check This.

Using NEWID() .

    select cast(RIGHT(CAST(CAST(NEWID() AS VARBINARY(36)) AS BIGINT), 14) as varchar(50))

OR Using rand()

      select convert(varchar(50),convert(numeric(14,0),rand() * 89999999999999))

Upvotes: 1

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

Try:

select CONVERT(VARCHAR(14),CONVERT(BIGINT,FLOOR(RAND() * POWER(CAST(10 as BIGINT), 14))))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Instead of FLOOR(), convert to a DECIMAL:

select CONVERT(DECIMAL(14, 0), RAND() * POWER(CAST(10 as BIGINT), 14))

Then convert this to a varchar:

select CONVERT(VARCHAR(14), 
               CONVERT(DECIMAL(14, 0), RAND() * POWER(CAST(10 as BIGINT), 14))
              )

Upvotes: 2

Related Questions