Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sorry if that's not overly clear.

In SQL Server, can I have a field which I can place a string into, and then SQL Server appends an auto-number onto the end?

So for example, say I have column A.

  • If I set row 1 in column A to "Foo", the final value will be "Foo1"
  • Then if I set row 2 in column A to "Bar", the final value is "Bar2"

Is this possible?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Yes use a Calculated Value column. For example:

ALTER TABLE Table1 ADD ColumnB AS ColumnA + CAST(ID AS varchar(8)) PERSISTED

Assuming ID is where you're getting row 1 or row 2 value.

share|improve this answer
    
Thanks very much! –  JMK Sep 16 '13 at 9:19

Another a way would be based on a view. If you can't modify the source table - often the case for 3rd party products - a view can save your day. Like so,

create table autoappend(id int identity(1,1) , data nvarchar(32))
go
insert into autoappend(data) values ('foo')
insert into autoappend(data) values ('bar')
insert into autoappend(data) values ('zof')
go

create view vAppend as
    select id
        , data
        , appended = data + convert(nvarchar(8), id)  -- concat() is SS2012+ only
    from autoappend
go

select * from vAppend

Output:

id  data    appended
1   foo foo1
2   bar bar2
3   zof zof3
share|improve this answer
    
appended is a column alias. I suggest you remove the single quotes. –  ypercube Sep 16 '13 at 11:10
    
@ypercube, Thanks. Didn't remember that. I Guess old bad habits are hard to kick –  vonPryz Sep 16 '13 at 11:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.