How to implement a primary key in SQL Server consists of alphanumeric and automatically grows by number

In SQL SERVER if we want to make the primary key grow automatically according to certain rules we can do this:

Here we have a new research table with research IDs, names of researchers and research hospitals.

We set the SICENTIFICID as the primary key and start at 1 each time accumulator 1.

And here you have to emphasize that the prerequisite for using the IDENTITY keyword is that the data type must be an int, decimal,bigint,smallint, and so on.

But a lot of times only a number does not meet our requirements, here the author's demand is to number according to the letter plus number of combinations, and the letter fixed and the number of increments of 1 each time.

So what do we do? I thought I could do it directly with database constraints, but I failed many times in my attempts.

Now, if you want to meet the requirements, you need to fix the database table.

We change the SCIENTIFICID data type of the Scientifica table to varchar (15).

However, how do we ensure that the data ID is increased according to certain rules, first of all, our letter prefix is fixed, where the ' Yanjiu ' is used as the letter prefix. such as ' yanjiu100000 '.

The next ID should be ' yanjiu100001 ', and in order to achieve our idea, using only one SQL statement is not possible, we need to write multiple.