6

I'd like to set a default value for a column from a sequence like what is done here, but also prepend a value in front of the sequence so the value saved in the table looks like P123. Is that possible?

Community
  • 1
  • 1
adam0101
  • 29,096
  • 21
  • 96
  • 174
  • would the letter always be P? Regardless, I don't think you can keep the sequence as an int. I'd virtualize another column based off your sequence columns. – S3S Mar 06 '17 at 22:21
  • @scsimon, in this case, yes it'd always be "P". By virtualize, do you mean like a computed column? Would I need two columns to do that (one for the sequence and one for the computed value)? Or how would that look? – adam0101 Mar 06 '17 at 22:24
  • correct on both. Sequence column would need to stay an int and the computed column would be a concatenation of this column and P. something like http://stackoverflow.com/a/24729386/6167855 – S3S Mar 06 '17 at 22:27
  • @scsimon, I just learned that I won't be able to do that because we will have to back-fill existing values from another system. So it needs to be a default value that we can explicitly set if there's an existing value. – adam0101 Mar 06 '17 at 22:31
  • 1
    @scsimon, nevermind. I reread through that post in my question and it appears that since the column just uses the sequence value as a default, an explicit value can still be added to that column and then the computed column will pick it up. – adam0101 Mar 06 '17 at 22:41
  • Oh nice, I did t see that – S3S Mar 06 '17 at 22:44

1 Answers1

5

It's totally possible.
Changing the example from the post you've linked to something like this:

create sequence mainseq as bigint start with 1 increment by 1;

create table mytable (
    id      varchar(20) not null constraint DF_mytblid default 'p' + CAST(next value for mainseq as varchar(10)),
    code    varchar(20) not null
)

Test:

INSERT INTO MyTable (Code) VALUES ('asdf'), ('cvnb')

SELECT *
FROM MyTable

Results:

id  code
p1  asdf
p2  cvnb
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121