How can I increment letters in a field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How can I increment letters in a field?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-18 : 07:27:36

Tcharly writes "I have a project to create a table to generate a user id, once first name, last name and social security number is entered. The user id will be seven characters long. Three letters and four numbers. I am planning on beginning with letters 'aaa', then 'aab', 'aac' and so on. How do I get that to automate?

Thanks,"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-18 : 09:34:39

declare @S varchar(26)
select @s = 'abcdefg...'
declare @i int, @j int, @k int, @cnt = 500
select @i = 1, @j = 1, @k = 0
while @cnt > 0
begin
select @cnt = @cnt - 1
select @k = @k + 1
if @k > 26
begin
select @k = 1, @j = @j + 1
if @j > 26
begin
select @j = 1, @i = @i + 1
end
end
end
insert tbl select substring(@s, @i,1) + substring(@s, @j,1) + substring(@s, @k,1)
end

or
create table #a (s varchar(1)
insert #a select 'a'
insert #a select 'b'
...

insert tbl
select top 500 s
from
(select s = t1.s + t2.s + t3.s
from #a t1 cross join #a t2 cross join #a t3
) a
order by s

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-18 : 09:48:50

In addition to nr's method, try this also

create table #Tmp(i int identity(0,1), Code varchar(10))

insert into #Tmp (Code)
select top 1000 Null
from syscolumns a cross join syscolumns b

select char(65+ (i/26/26)%26) + char(65 + (i/26)%26) + char(65 + i%26)
from #tmp

drop table #tmp

Madhivanan

Failing to plan is Planning to fail

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-18 : 10:41:41

Oh I like that one madhivanan.
Good show!

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources