Need help to populate a column

Question

Post reply

Need help to populate a column

RoadRunner

SSC-Addicted

Points: 409
More actions
June 25, 2004 at 11:26 am

#108711

I have to create a table with a three-character column that contains all the combinations from AAA to Z99.
How can I get a query to populate every combination; AAA, AAB, AAC . . . AA0, AA1 . . . . . Z99?
The first char ranges from A to Z. The second and third char ranges from A to Z then 0 to 9.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

Use this to build the loops

declare @i int, @j-2 int

select @i = 1

while @i < 36

begin

if @i < 27

select @j-2 = @i + 64

else

select @j-2 = @i + 48 - 27

print char(@j)

select @i = @i + 1

end

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Note you need to change

while @i < 36

to

while @i < 37

or you will miss 9

However building on Steves here is an example to give you what you want.

set nocount on

if object_id('tempdb..#tmpX') is not null

drop table #tmpX

CREATE TABLE #tmpX (

valX char(1) primary key

)

declare @i int, @j-2 int

set @i = 1

while @i < 37

begin

insert #tmpX (valX) select char(@i + (case when @i < 27 then 64 else 21 end))

set @i = @i + 1

end

select (A.valX + B.valX + c.valX) Val from #tmpX A inner join #tmpX B on 1=1 and IsNumeric(A.valX) = 0 cross join #tmpX C

drop table #tmpX

Aaron West SSC Eights! Points: 808 More actions · Answer 3

Fun with cross products... I just saw this technique in a post.

create view Letters AS

SELECT 'A' l UNION ALL

SELECT 'B' UNION ALL

SELECT 'C' UNION ALL

SELECT 'D' UNION ALL

SELECT 'E' UNION ALL

SELECT 'F' UNION ALL

SELECT 'G' UNION ALL

SELECT 'H' UNION ALL

SELECT 'I' UNION ALL

SELECT 'J' UNION ALL

SELECT 'K' UNION ALL

SELECT 'L' UNION ALL

SELECT 'M' UNION ALL

SELECT 'N' UNION ALL

SELECT 'O' UNION ALL

SELECT 'P' UNION ALL

SELECT 'Q' UNION ALL

SELECT 'R' UNION ALL

SELECT 'S' UNION ALL

SELECT 'T' UNION ALL

SELECT 'U' UNION ALL

SELECT 'V' UNION ALL

SELECT 'W' UNION ALL

SELECT 'X' UNION ALL

SELECT 'Y' UNION ALL

SELECT 'Z' L

go

CREATE VIEW digits AS SELECT '0' D UNION ALL

SELECT '1' UNION ALL

SELECT '2' UNION ALL

SELECT '3' UNION ALL

SELECT '4' UNION ALL

SELECT '5' UNION ALL

SELECT '6' UNION ALL

SELECT '7' UNION ALL

SELECT '8' UNION ALL

SELECT '9'

go

CREATE VIEW alnum AS

SELECT L AS A FROM letters UNION ALL

SELECT D AS A FROM digits

go

SELECT L.L+A.A+A2.A mycol [into newtable]

FROM Letters L, alnum A, alnum A2

Guess you could make that last query a view too...

Aaron West SSC Eights! Points: 808 More actions · Answer 4

okay now I feel silly; the results are out of order. So if the order is important go with the looping, or create a table for alnum instead of using a view, and use order by on numbers and use case expressions as mentioned before...

Given a table of at least 36 numbers, this should work:

SELECT L.L+A.L+A2.L acro

FROM (SELECT TOP 26 CHAR(Number+64) L FROM Numbers) L

,(SELECT TOP 36 Number, CHAR(Number + CASE WHEN Number>26 THEN 21 ELSE 64 END) L FROM Numbers) A

,(SELECT TOP 36 Number, CHAR(Number + CASE WHEN Number>26 THEN 21 ELSE 64 END) L FROM Numbers) A2

ORDER BY L.L, A.Number, A2.Number

If you need the Numbers table, this should work to create it:

SELECT TOP 36 Identity(int,1,1) Number INTO Numbers

FROM sysobjects s, sysobjects s2

RoadRunner SSC-Addicted Points: 409 More actions · Answer 5

Thank you all for the help! This is what I did based on the first reply. First I created a table with a three character column then ran this query and it gave me every combo I needed. Again, thanks-a-million!

declare @g int, @h int, @i int, @j-2 int, @k int, @l int

set @g = 1

while @g < 27

begin

set @l = @g + 64

set @i = 1

while @i < 37

begin

if @i < 27

begin

set @j-2 = @i + 64

set @h = 1

while @h < 37

begin

if @h < 27

set @k = @h + 64

else

set @k = @h + 48 - 27

insert into wps_3 (wpn_cd) values (char(@l)+char(@j)+char(@k))

set @h = @h + 1

end

else

begin

set @h = 1

set @j-2 = @i + 48 - 27

while @h < 37

begin

if @h < 27

set @k = @h + 64

else

set @k = @h + 48 - 27

insert into wps_3 (wpn_cd) values (char(@l)+char(@j)+char(@k))

set @h = @h + 1

end

set @i = @i + 1

end

set @g = @g + 1

end

RoadRunner SSC-Addicted Points: 409 More actions · Answer 6

After looking at all the posts this seems the best code:

declare @i int, @j-2 int, @k int

set @k = 1

while @k < 27

begin

set @j-2 = 1

while @j-2 < 37

begin

set @i = 1

while @i < 37

begin

insert into wps_3 (wpn_cd) values (char(@k + 64)+char(@j + (case when @j-2 < 27 then 64 else 21 end))+char(@i + (case when @i < 27 then 64 else 21 end)))

set @i = @i + 1

end

set @j-2 = @j-2 + 1

end

set @k = @k + 1

end

MikeMcDonald Say Hey Kid Points: 663 More actions · Answer 7

Rather late! But here's another way of doing it ..

declare @tab1 table (letter char(1))
declare @tab2 table (letter char(1))
declare @tab3 table (letter char(1))
insert into @tab1 values ('A')
insert into @tab1 values ('B')
insert into @tab1 values ('C')
insert into @tab1 values ('D')
insert into @tab2 select letter from @tab1
insert into @tab2 values ('0')
insert into @tab2 values ('1')
insert into @tab3 select letter from @tab2
select t1.letter+t2.letter+t3.letter
from @tab1 t1 cross join @tab2 t2 cross join @tab3 t3

Expand the inserts to go to Z and 9

Michael