Generate unique string in sql server 2012

Question

I have a table

CREATE TABLE #Agency 
 (
  AgencyID int PRimary KEY IDENTITY(1,1),
  AgencyName varchar(100), 
  AgencyCode varchar(50)
  )

I want to generate a unique agency code while inserting into the table...Agency code should consists of only Alphabets ..no numbers or special characters

Thank you

Is it necessary to avoid numbers or special characters? If that was acceptable, you could use the NEWID() function to generate a GUID for you. Details here: dailycoding.com/Posts/… — havardhu
– havardhu, Commented Apr 25, 2013 at 10:41
That sounds like you are generating a natural key in the database. Could turn into a tin of worms later on that. — Tony Hopkinson
– Tony Hopkinson, Commented Apr 25, 2013 at 11:26
yes ..i know that GUID option..but it is compulsary to take only alphabets.. — SQL006
– SQL006, Commented Apr 25, 2013 at 11:52

Nenad Zivkovic · Accepted Answer · 2013-04-25 12:14:37Z

3

You can do something as simple as casting your ID to varchar and replacing every digit with letter (or combination).

1=A, 2=B, etc..

So, as long your IDs are unique, so will the code be.

Snippet could be something like:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    CAST(AgencyID AS VARCHAR(50))
    ,'1','A'),'2','B'),'3','C'),'4','D'),'5','E'),'6','F'),'7','G'),'8','H'),'9','I'),'0','J')

If you want to do it during insert, you can use IDENT_CURRENT('tablename') to get the value that inserts for identity column.

      INSERT INTO Agency (AgencyName, AgencyCode)
    SELECT 'Name001', 
              REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
              CAST(IDENT_CURRENT('Agency') AS VARCHAR(50))
              ,'1','A'),'2','B'),'3','C'),'4','D'),'5','E'),'6','F'),'7','G'),'8','H'),'9','I'),'0','J')

;

SQLFiddle DEMO

Creating a function that converts your number to string and/or computed column might be prettier for use.

DEMO using function and computed columns

EDIT:

DEMO with updated request to make at least 5 string long character

edited Apr 25, 2013 at 12:14

answered Apr 25, 2013 at 10:56

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

usr Over a year ago

My answer is kind of an alternative view on the question, but you're answering directly, so +1.

SQL006 Over a year ago

thanks for the answer..but the code should have atleast 5 characters

Nenad Zivkovic Over a year ago

@SQL006 You can add additional logic to function creating code before Return. Maybe if it's shorter than 5 characters to add few X letters in front. Check new demo in edited post.

usr · Accepted Answer · 2013-04-25 11:01:08Z

1

I recommend that you generate the string in the application. That way you can use a language that is better suitable for procedural code than T-SQL is. It is best-practice anyway to keep logic out of SQL Server if possible.

answered Apr 25, 2013 at 11:01

usr

172k38 gold badges251 silver badges380 bronze badges

Collectives™ on Stack Overflow

Generate unique string in sql server 2012

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related