10 digit random unique alpha-numeric number

Question

Post reply

10 digit random unique alpha-numeric number

Mak Mohan

SSC Eights!

Points: 994
More actions
January 11, 2010 at 11:39 pm

#73977

Hi
How to generate 10 digit random unique alpha-numeric string in sql server 2000?
Thanks in advance.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

What does it mean "unique"?

_____________
Code for TallyGenerator

Mak Mohan SSC Eights! Points: 994 More actions · Answer 2

I need to generate a random 10 digit alphanumeric string that is also unique within a table.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Abhijeet Dighe (1/11/2010)
Hi
How to generate 10 digit random unique alpha-numeric string in sql server 2000?
Thanks in advance.

Save yourself from a lifetime of misery and pain, do it properly and use 36 characters.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Here is an excellent function developed by Lynn Pettis ...

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyNewID]'))

EXEC dbo.sp_executesql @statement = N'create view [dbo].[MyNewID] as

select newid() as NewIDValue;

'

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_RandomString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

BEGIN

execute dbo.sp_executesql @statement = N'CREATE function [dbo].[ufn_RandomString](

@pStringLength int = 10 --set desired string length

) returns varchar(max)

/* Requires View create view dbo.MyNewID as select newid() as NewIDValue;

By Lynn Pettis in SQL Musings from the Desert | 04-04-2009 10:01 PM */

as begin

declare @RandomString varchar(max);

with

a1 as (select 1 as N union all

select 1 union all

select 1),

a2 as (select

1 as N

from

a1 as a

cross join a1 as b),

a3 as (select

1 as N

from

a2 as a

cross join a2 as b),

a4 as (select

1 as N

from

a3 as a

cross join a2 as b),

Tally as (select

row_number() over (order by N) as N

from

a4)

, cteRandomString (

RandomString

) as (

select top (@pStringLength)

substring(x,(abs(checksum((select NewIDValue from MyNewID)))%36)+1,1)

from

Tally cross join (select x=''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'') a

)

select @RandomString =

replace((select

'','' + RandomString

from

cteRandomString

for xml path ('''')),'','','''');

return (@RandomString);

end

'

END

-- Use AS SELECT dbo.ufn_RandomString(10)

If you do not have a Tally table read this article by Jeff Moden

http://www.sqlservercentral.com/articles/T-SQL/62867/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Mak Mohan SSC Eights! Points: 994 More actions · Answer 5

Mak Mohan

SSC Eights!

Points: 994

January 12, 2010 at 11:58 pm

#1102896

Hi

Thanks for your solution

arun.sas SSChampion Points: 11831 More actions · Answer 6

Hi Abhijeet,

Big bucket mentioned code for random of the alphanumeric is fine, but not suit for the SQL 2000, you use the newid() to get the simple random like

select CONVERT(varchar(10), right(newid(),10))

Mak Mohan SSC Eights! Points: 994 More actions · Answer 7

Hi

Thanks. This looks much simpler.

But will it generate unique string for a table?

arun.sas SSChampion Points: 11831 More actions · Answer 8

Hi,

Unique string for the table/procedure

Declare @random varchar(10)

set @random = CONVERT(varchar(10), right(newid(),10))

apply this @random string in the table/procedure

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

You cant generate a unique random number , you will have to generate a random number then test for uniqueness.

Heres how i generate random strings

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/a-faster-tsql-random-length-random-string-generator.aspx

-- EDIT : Apologies , only just noticed you need a 2000 solution

Clear Sky SQL
My Blog[/url]

Mak Mohan SSC Eights! Points: 994 More actions · Answer 10

Hi

So, newid() function only generates random no.

Will I have to check for its uniqueness?

arun.sas SSChampion Points: 11831 More actions · Answer 11

Abhijeet Dighe (1/12/2010)
I need to generate a random 10 digit alphanumeric string that is also unique within a table.

Hi,

This @random string value is unique unless you pass it again.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Abhijeet Dighe (1/13/2010)
Hi
So, newid() function only generates random no.
Will I have to check for its uniqueness?

According to BOL, NEWID() "Creates a unique value of type uniqueidentifier."

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mak Mohan SSC Eights! Points: 994 More actions · Answer 13

Thanks.

But if I take only 10 characters of string generated using newid() like this:

select CONVERT(varchar(10), right(newid(),10))

still will it generate unique string?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 14

Abhijeet Dighe (1/14/2010)
Thanks.
But if I take only 10 characters of string generated using newid() like this:
select CONVERT(varchar(10), right(newid(),10))
still will it generate unique string?

No , but it may be unique enough for you purposes.

You will have to check that it is truly unique against your data.

Clear Sky SQL
My Blog[/url]