Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How using SQL to generate random string?


How using SQL to generate random string?

Author
Message
Sharul Nizam
Sharul Nizam
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 124
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
Garadin
Garadin
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 Visits: 4107
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Sharul Nizam
Sharul Nizam
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 124
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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
Unfortunately, you really didn't answer Seth's question. But regardless, please check out my blog post here.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sharul Nizam
Sharul Nizam
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 124
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?
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 25280
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

Before posting a performance problem please read
Garadin
Garadin
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 Visits: 4107
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Sharul Nizam
Sharul Nizam
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 124
thanks for the guide.

me still develop the T-SQL
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923


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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search