Autogenerated String Values - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Autogenerated String Values

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2011-09-02 : 10:30:09

Hi,
I would like to create column with autogenerated 5-Character string like following...

SELECT '00001' AutogeneratedId UNION ALL
SELECT '00002' UNION ALL
SELECT '00003'....
...
...
SELECT '00010'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-09-02 : 10:37:56

If you have a numbers table in your database, you can use that to generate the sequence of numbers. Another way would be to use a recursive CTE like this:


WITH N(n) AS
(
	SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 10
) 
SELECT RIGHT('00000'+CAST(n AS VARCHAR(5)),5) FROM N OPTION (MAXRECURSION 10);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources