Create a random string of digits

Question

I need to create a random string of digits of given length

CREATE FUNCTION UniqConvert
(@calue as varchar(max),
@len as int)

The output should be of the length @len and should be unique per input @value

I already asked similar question: Create random string of digits T-SQL This one has different concept

Question seems confused. It sounds like you want to hash an input, but also have variable length output. Are you sure about that? If the output length is shorter than a vanilla hash you will increase the chance of collisions. If larger than a normal hash you will gain nothing, and possibly struggle to use the hash. — PaulG
– PaulG, Commented Apr 26, 2014 at 12:52
The issue i have is the 'len' stipulation with varchar input and 'uniqueness'. Imagine you pass 'A'.. 'Z' in turn and ask for len = 1. There is an obvious collision. You may know what you require and never do this. Your question does not state that and i think it should. I know it is a silly example but it shows the point i am trying to make. — Ryan Vincent
– Ryan Vincent, Commented Apr 26, 2014 at 13:19
Have you considered using one of the 'crypto' hash functions? i know they are expensive but, at first look, would seem to do most what you want. Actually, i am guessing as i do not know your 'use case'. — Ryan Vincent
– Ryan Vincent, Commented Apr 26, 2014 at 13:30

vignesh · Accepted Answer · 2014-04-26 17:26:37Z

0

declare @len int
set @len = 3
DECLARE @Random INT      
DECLARE @Upper INT      
DECLARE @Lower INT
SET @Lower = power(10,@Len-1) ---- The lowest random number      
SET @Upper = power(10,@Len )-1 ---- The highest random number      
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) 

select  @Random

answered Apr 26, 2014 at 17:26

vignesh

1,4965 gold badges19 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Reza · Accepted Answer · 2014-04-26 12:50:39Z

0

Try this

   select Cast(Round(Rand()*power(10,@Len),0) as Varchar(30))

answered Apr 26, 2014 at 12:50

Reza

20.1k18 gold badges102 silver badges178 bronze badges

Collectives™ on Stack Overflow

Create a random string of digits

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related