November 21, 2009 at 5:45 am
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
November 21, 2009 at 11:01 am
Before we give you crazy solutions to accomplish this, could you share the reason for it and what you hope to accomplish?
November 21, 2009 at 7:25 pm
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
November 22, 2009 at 8:36 am
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:
November 22, 2009 at 8:36 am
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?
November 22, 2009 at 12:51 pm
thanks for the guide.
me still develop the T-SQL
November 22, 2009 at 12:56 pm
Here's another method (requires 2005+)
November 22, 2009 at 3:51 pm
Dave Ballantyne (11/22/2009)
Here's another method (requires 2005+)
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
Change is inevitable... Change for the better is not.
November 22, 2009 at 4:26 pm
[Dave Ballantyne (11/22/2009)
--------------------------------------------------------------------------------
Here's another method (requires 2005+)
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]
November 22, 2009 at 7:43 pm
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
Change is inevitable... Change for the better is not.
November 22, 2009 at 7:52 pm
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
Change is inevitable... Change for the better is not.
November 23, 2009 at 1:53 am
Hee Hee, No worries Jeff, constructive criticism always welcome 😛
November 23, 2009 at 5:59 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply