Auto Generate a table with 1000,000 unique strings
-
Help?
I need to populate a table with a 6 Character unique String. 1,000,000 to be exact. Here are My requirements.
It must Be in SQL.
It must contain 6 Caharacters consisting of at least 1 Number.
It Must be unique.
It Cannot contain the Letter A,I or numbers 1, 0
For example
AAAAA2, AAAAA3, AAAAA4, AAAAA5, AAAAA6, AAAAA7, AAAAA8, AAAAAA9,
B22236,
I have no Idea how to build someting like this in just SQL. Does anyone have any idea where to start?
Thank you
-
you said:
"It Cannot contain the Letter A,I or numbers 1, 0"
but in the examples
AAAAA2, AAAAA3, AAAAA4, AAAAA5, AAAAA6, AAAAA7, AAAAA8, AAAAAA9,
B22236,
you used the letter "A"
* Noel -
here is something to get you started:
select number as n into #T1 from master..spt_values where number < 100 and type ='P'
select dbo.fn_varbintohexstr( t1.n + 100*t2.n + 10000 * t3.n)
from #T1 t1 cross join #T1 t2 cross join #T1 t3
Cheers,
* Noel -
Interesting.
I decided to use the 1st 6 characters of a GUID.
Set NoCount On
Declare @Count int
Set @Count = 0
Declare @NextString varchar(6)
Declare @TestTable Table (
Column1 Varchar(6) Not Null primary key clustered
)
While @Count < 1000000
Begin
Select @NextString= left(convert(varchar(50), newid()),6)
Insert Into @TestTable
Select @NextString
Where @NextString Not Like ('%[AI10]%') -- Can't contain A or I or 1 or 0
And @NextString Like ('%[2-9]%') -- must have at least 1 number
And Not Exists ( -- must be unique & not already in the table
Select * From @TestTable
Where Column1 = @NextString
)
Select @Count = @Count + @@rowcount
End
Select count(*) From @TestTable
-
Sorry, I meant to say 'o'
I'm checking these suggestion out now.
Thank you all for your help!
-
OK now that you have specified the problem you can modify my solution as:
select number as n into #T1 from master..spt_values where number < 100 and type ='P'
select Replace ( Replace ( Replace ( dbo.fn_varbintohexstr( t1.n + 100*t2.n + 10000 * t3.n), '0x0000','kk'), '0','z'), '1','l')
from #T1 t1 cross join #T1 t2 cross join #T1 t3
Cheers
* Noel -
Noeld,
this problem looks interesting and I was curious about your solution... however, either I have different function dbo.fn_varbintohexstr, or it is due to some settings, or else there is a flaw in your code. I'm getting strings that don't correspond to the requirements "must have 6 characters, at least one of them is a number". Your code returned - together with valid values - also such as 'kkzzzz' (no numbers) or 'zxzzz22z68' (too long).
It is not explicitly mentioned in the requirements, but I suppose that all characters should be UPPER case... because if it isn't so, then also the character l (lower L) should be excluded. At least I suppose that these restrictions are meant to avoid confusion with characters similar to numbers (O vs. 0).
-
If you are using SQL 2005 you can use recursive SQL to generate your data.
The 'DB2 SQL Cookbook' has some interesting examples of this that would work OK in SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply