How using SQL to generate random string?

Question

How using SQL to generate random string?

Sharul Nizam

Ten Centuries

Points: 1302
More actions
November 21, 2009 at 5:45 am

#72553

My statement as follow,
create table tStrRndm
(
BoardCd varchar(6) -- this is a unique
)
DECLARE @myString AS varchar(50)
SET @myString= '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @gStrRndm as varchar(6)
1. I'm looking a solution to build a T-SQL to generate random string from @myString?
2. Random string is a 6 character
3. Let's say, random string generated is 7MW72X
4. If random string = 7MW72X exist in a tStrRndm, then SQL re-generate random string --- i believe this in a loop
5. If random string = 7MW72X not exist in a tStrRndm, random string insert into tStrRndm and exit loop. As a result @gStrRndm=random string
Looking for help to built the T-SQL for above scenario

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Before we give you crazy solutions to accomplish this, could you share the reason for it and what you hope to accomplish?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 2

Garadin (11/21/2009)
Before we give you crazy solutions to accomplish this, could you share the reason for it and what you hope to accomplish?

1. Me just want to see, Stored Procedure can comply this or not

2. If Stored Procedure can't doing this, me have no choice. As a result, the coding will be at front-end

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Unfortunately, you really didn't answer Seth's question. But regardless, please check out my blog post here.

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 4

Lynn Pettis (11/21/2009)
Unfortunately, you really didn't answer Seth's question. But regardless, please check out my blog post here.

Just saw your great article.

How to make it random string in 6 character and always unique?

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

Sharul Nizam

Is your question about how to use that fantastic function developed by

Lynn Pettis ? Assuming it is here is a start .

If it is to insure the string is unique define your table with a unique index for example:

CREATE TABLE [dbo].[tStrRmdm](

[BoardCd] [varchar](6) NOT NULL

) ON [PRIMARY]

END

GO

SET ANSI_PADDING OFF

GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tStrRmdm]') AND name = N'IX_tStrRmdm')

CREATE UNIQUE NONCLUSTERED INDEX [IX_tStrRmdm] ON [dbo].[tStrRmdm]

(

[BoardCd] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Then you can execute Lynn Pettis function multiple times to insert the generated random string into the table. If the generated string is not unique an error message will be returned and the "duplicate" will not be inserted into the table.

If you do not know what a Tally table is read Jeff Moden's article on SSC at:

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]

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Sharul Nizam (11/21/2009)
Garadin (11/21/2009)
Before we give you crazy solutions to accomplish this, could you share the reason for it and what you hope to accomplish?
1. Me just want to see, Stored Procedure can comply this or not
2. If Stored Procedure can't doing this, me have no choice. As a result, the coding will be at front-end

There are very few limitations on what you *can* do in T-SQL. There are considerably more on things you *should* do. Could you set up a loop with a string generator to check for whether or not it exists in the table and if so, generate a new one until it does not? Yes. Should you? It depends... but I certainly can't come up with a valid reason to do so. Which is why I want to hear what your actual requirement is... there may be a much better way to accomplish your actual goal. All the different limitations you put on it combine to make this considerably more convoluted.

For example:

Does it really need to be random?

Does it really need to be 6 characters or can it be longer?

Does it really need to use all the characters of the alphabet and 0-9?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 7

Sharul Nizam

Ten Centuries

Points: 1302

November 22, 2009 at 12:51 pm

#1082735

thanks for the guide.

me still develop the T-SQL

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 8

Here's another method (requires 2005+)

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

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Dave Ballantyne (11/22/2009)
Here's another method (requires 2005+)
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/a-faster-tsql-random-length-random-string-generator.aspx

Heh... I always have to laugh at articles like that... they talk about performance and "better" ways to do things but they all end up making the same horrible error that almost immediately discredits what ever they have to say whether it be good or bad... they use a bloody While Loop to gen the numbers table they're getting ready to use. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

[Dave Ballantyne (11/22/2009)
--------------------------------------------------------------------------------
Here's another method (requires 2005+)
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/a-faster-tsql-random-length-random-string-generator.aspx%5B/quote%5D
Heh... I always have to laugh at articles like that... they talk about performance and "better" ways to do things but they all end up making the same horrible error that almost immediately discredits what ever they have to say whether it be good or bad... they use a bloody While Loop to gen the numbers table they're getting ready to use.
--Jeff Moden

Jeff that's why in telling the OP to use Lynn Pettis' function I include a link to your TALLY table article ... hopefully it will be read and understood

/quote]

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]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

No problem with what you posted... I understood what you were doing, Ron and I appreciate it because you're exactly right... folks need to get away from thinking "loops".

I just get a real kick out of some of the "experts" that still think it's ok to post looping code to build a Tally table because "you only need to do it once". They miss the perfect opportunity to teach something the right way and, instead, seem to justify the use of the loop. The folks that read that crud and don't know otherwise, might think it's ok to use a loop in other places. "Well, this performance expert on the internet used a loop to create a Numbers table...". They just don't get it nor do they understand the missed opportunity.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

Heh... crap... I didn't realize that it was Dave's article. Didn't mean to blast one of our own but I guess everyone know's I'm serious about it now.

Sorry Dave and shame on you ;-):-P:hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 13

Hee Hee, No worries Jeff, constructive criticism always welcome 😛

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

Dave Ballantyne (11/23/2009)
Hee Hee, No worries Jeff, constructive criticism always welcome 😛

Heh... you're taking it well. Thanks. Just curious... does that mean you're going to change the article?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)