0

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
milner236
  • 37
  • 9
  • 3
    Use `CONCAT` not `+`. – Thom A Feb 03 '22 at 15:40
  • Assuming you have the ability to create table in the database, you can concatenate 'KH' to an Identity column of your choice to generate these values. – Arpit Chinmay Feb 03 '22 at 15:49
  • `+` is fine on Sql Server, but MySql and some other databases don't like it. – Joel Coehoorn Feb 03 '22 at 15:51
  • @JoelCoehoorn plus is not fine on SQL server if one column is int and the other is varchar. This can be done with *CONCAT* – t-clausen.dk Feb 03 '22 at 15:56
  • `CREATE TABLE #Dummy ( DummyID INT IDENTITY(1, 1) NOT NULL, DummyPrimaryKey AS 'KH'+'000' +CAST(DummyID AS VARCHAR(30)), DummyCharacter varchar(1) /*Insert other column as required*/ )` You can insert data directly not having to worry about adding your autoincreament column. – Arpit Chinmay Feb 03 '22 at 16:00
  • Thank you @Larnu. This works great and is exactly what I was missing. – milner236 Feb 03 '22 at 16:00
  • Does this answer your question? [Create an auto incrementing alpha numeric primary key in SQL Server Management Studio](https://stackoverflow.com/questions/68252718/create-an-auto-incrementing-alpha-numeric-primary-key-in-sql-server-management-s) – Charlieface Feb 03 '22 at 17:24

2 Answers2

2

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))
GuidoG
  • 11,359
  • 6
  • 44
  • 79
0
string + cast(string2 as varchar(10)) as <column name>
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794