Auto generate String

I have an ID Field in a table ContactInformation. This ID field is char(4)
type. And for this I want to auto generate strings starting from '0001'
onwards:
0001
0002
9998
9999
A001
A002
Z999
Thanks.You can't auto generate strings like an Identity() does. You have to create
a stored procedure that you call to get the next value and use that in the
Insert.
Andrew J. Kelly SQL MVP
"SQL Newbie" <SQL Newbie@.discussions.microsoft.com> wrote in message
news:97A8948F-934A-4DE9-A3F4-125C1847C3AD@.microsoft.com...
>I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||This seems like a very loopy and bizarre numbering scheme.
If you could allow A000 instead of jumping from 9999 to A001, there might be
an easier way to do this (if you also allow a peripheral IDENTITY column),
e.g.
create table dbo.foo
(
int_id int identity(1,1),
id as convert(char(4),
CASE WHEN int_id < 10000 THEN RIGHT('0000'+RTRIM(int_id),4)
ELSE CHAR(CONVERT(INT,LEFT(RTRIM(int_id),2))+
54) +
RIGHT('000'+RTRIM(int_id),3)
END)
)
SET NOCOUNT ON;
WHILE 1=1
BEGIN
BEGIN TRAN
INSERT foo DEFAULT VALUES
COMMIT
END
Don't forget to stop the above loop after a few minutes! Then run SELECT *
FROM foo and you will see that it closely matches your requirements, until
you hit somewhere around 36,000 rows. If you can't have the extra column
then you could easily use a stored procedure to handle inserts into the
eventual table, by first inserting into this table and then grabbing the row
that matches scope_identity(), and inserting into the "real" table. However
that would serialize inserts, would break on a multi-insert statement (or
BULK INSERT or bcp), and would require you to prevent ad hoc direct inserts
to the table. In addition, I have no idea what you expect to do once you
get past ~36,000 rows, where your CHAR(4) "numbering" scheme breaks. It
will loop around and perform lower case a000 -> z999 and then, at some
point, the upper bound will exceed the capacity of CHAR() and the inserts
will stop working. You can stop it at Z999 (and produce errors thereafter,
at least for the next 36,000 rows) if you apply a unique constraint to the
id column (provided you are in a case insensitive collation).
Have fun. Glad I'm not directly involved, I would probably move on to a
different gig. Your requirements seem to have been drafted by someone who
does not understand how databases work and, in particular, the weaknesses of
rolling your own string generator to create the same kind of meaningless
surrogate key that could easily be handled by identity alone, where the
system justtakes care of itself and you don't have any of these constraints,
extra space requirements and logical headaches...
"SQL Newbie" <SQL Newbie@.discussions.microsoft.com> wrote in message
news:97A8948F-934A-4DE9-A3F4-125C1847C3AD@.microsoft.com...
>I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||Hi,
I would fully agree with Aaron that you will pretty soon reach your limit. I
believe one thing that you might wanna do is to increase char(4) to a higher
value depending on how many inserts you want. This way you can also avoid an
additional over head of including alphabets in your ID field.
Its is always a good idea to conduct a capacity planning (as to how many
such id you would need) well in advance before coding such things.
I believe you need this particular format (i.e. to say that ID should have
to have these many characters which is not a very uncommon situation.) and
padding the left side characters with zero is often time used. But once agai
n
these do have limits to the max number they can avhieve.
Hope this helps.
"SQL Newbie" wrote:

> I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||Why? Do these values have a special meaning in your data model?
ML|||> I believe you need this particular format (i.e. to say that ID should have
> to have these many characters which is not a very uncommon situation.) and
> padding the left side characters with zero is often time used.
But aside from the weird rollover scheme used here (rolling to A -> Z after
the first 9,999 rows), this could easily be achieved through a view or
stored procedure, without storing this unnecessary padding in the database.
Let's say we could get by with just
00001
00002
...
00578
...
09999
...
99999
Then you could easily use an IDENTITY column and then create a view like:
CREATE VIEW dbo.bar
AS
SELECT RIGHT('00000'+RTRIM(id_column)) FROM some_table|||Thank you very much Aaron.
"Aaron Bertrand [SQL Server MVP]" wrote:

> But aside from the weird rollover scheme used here (rolling to A -> Z afte
r
> the first 9,999 rows), this could easily be achieved through a view or
> stored procedure, without storing this unnecessary padding in the database
.
> Let's say we could get by with just
> 00001
> 00002
> ...
> 00578
> ...
> 09999
> ...
> 99999
> Then you could easily use an IDENTITY column and then create a view like:
> CREATE VIEW dbo.bar
> AS
> SELECT RIGHT('00000'+RTRIM(id_column)) FROM some_table
>
>