How generate character unique id

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 generate character unique id

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-07 : 08:11:35

kirankumar writes "Please let me know How to generate 5 unique character id
from database tablesuppose table have a field username varchar(20)
by entering any name i want to generate similar id as like when we are creteing new mail account on any mail site."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-07 : 08:22:00

maybe you can look into right(newid(), 5) or left(newid(), 5)

-----------------
[KH]

Kristen
Test

22859 Posts

Posted - 2005-12-07 : 13:22:28

You could use a

SELECT MAX(MyColumn)
FROM MyTable
WHERE MyColumn LIKE 'ABCDE%'

and then "increment" the value you get returned and use that as the new value.

The increment will have to take care of any alphanumeric characters that are used.

Alternatively you could have a "tie break" numeric column. So an account "ABCDE" would actually be shown as "ABCDE123", where the "123" part was stored in a separate column:


SELECT COALESCE(MaxTieBreak+1, 1)  -- Next available tie break number
FROM
(
    SELECT MAX(MyTieBreak) AS MaxTieBreak
    FROM MyTable
    WHERE MyColumn = 'ABCDE'
) X

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources