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, Apr 26 '14 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, Apr 26 '14 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, Apr 26 '14 at 13:30

score 0 · Answer 1 · answered Apr 26 '14 at 12:50

0

Try this

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

answered Apr 26 '14 at 12:50

Reza

18,865
13
88
163

score 0 · Accepted Answer · answered Apr 26 '14 at 17:26

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

Create a random string of digits

2 Answers2