Auto Increment with Numbers and Letters

Question

I am trying to auto increment with numbers and letters. It should look like this:

KH001
KH002
KH003
etc..

I've looked at a couple of articles and other Stack Overflow questions but couldn't manage to understand as SQL is my much weaker side.

I have created a column named string that had varchar(50) data type and a fixed value of KH. I created a second column named string2 that had int data type and auto increment by 1 that works.

Then I added the following line to my query:

string + string2 as <column name>

Received an error saying I couldn't convert 'KH' to int.

Answer 1

here are 2 simple methods you can use

declare @string varchar(10) = 'KH'
declare @string2 int = 1

select concat(@string, @string2),
       @string + convert(varchar, @string2)

More info about the Concat funtion

You can also add a computed column in your table that does this for you,

    alter table yourtable 
    add mycolumn as concat(string, string2)

Read more about computed columns here

To get zeros left of your number, you can do this

concat(@string, RIGHT ('00000' + Convert(nvarchar(20), @string2), 5))

Answer 2

string + cast(string2 as varchar(10)) as <column name>