Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

How using SQL to generate random string? Expand / Collapse
Author
Message
Posted Saturday, November 21, 2009 5:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 21, 2010 10:19 PM
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
Post #822871
Posted Saturday, November 21, 2009 11:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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
Post #822917
Posted Saturday, November 21, 2009 7:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 21, 2010 10:19 PM
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
Post #822947
Posted Saturday, November 21, 2009 8:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:15 PM
Points: 22,898, Visits: 31,303
Unfortunately, you really didn't answer Seth's question. But regardless, please check out my blog post here.



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)
Post #822951
Posted Sunday, November 22, 2009 1:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 21, 2010 10:19 PM
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?
Post #822961
Posted Sunday, November 22, 2009 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:04 PM
Points: 5,552, Visits: 24,457
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
Post #822975
Posted Sunday, November 22, 2009 8:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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
Post #822976
Posted Sunday, November 22, 2009 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 21, 2010 10:19 PM
Points: 70, Visits: 124
thanks for the guide.

me still develop the T-SQL
Post #823004
Posted Sunday, November 22, 2009 12:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:54 AM
Points: 1,949, Visits: 8,301
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
Kent user group
Post #823005
Posted Sunday, November 22, 2009 3:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:46 PM
Points: 36,612, Visits: 31,053


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #823026
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse