Populating an nvarchar column with sequential values

Transact-SQL
1

I found this https://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/ but the problem is that my column is not an int but an nvarchar. Also, I need the column values should be populated with C100001...C10002...

I tried this but I get a conversion error.
DECLARE @id int
SET @id = 10000
UPDATE tablename
SET @id = id = 'C' + CAST(@id as nvarchar(50)) + 1

How can I rewrite this?

2

DECLARE @tbl TABLE ( id NVARCHAR(50) )
INSERT INTO @tbl(id)
VALUES('C')

DECLARE @id int
SET @id = 10000

SELECT * FROM @tbl

UPDATE @tbl
SET id = id+CAST (@id AS VARCHAR(50))

SELECT * FROM @tbl

3

Maybe something like (not tested):

UPDATE TableName SET ID = 'C' + CAST(ROW_NUMBER() OVER(ORDER BY ID)+10000 AS NVARCHAR(10))
Note that this works for a select but I did not try for an update.

1 Like
4

You could use a computed column to automatically generate the nvarchar column value:

CREATE TABLE table_name (
    id int IDENTITY(100001, 1) NOT NULL,
    seq_value AS CAST('C' + CAST(id AS nvarchar(10)) AS nvarchar(50)),
    ...
6

If the prefix is always "C" I would not want to hold it in the column, so I too would use Scott's approach of "faking" the Prefix "C"

If the "C" can change, over time, then I would have separate columns for the Prefix and the ID and then "fake" the composite column.

What happens when the ID rolls-over to the next order of magnitude? Is

C100001
C1000001

OK, or would it be better to have

C0100001
C1000001

?

With a "faked" column it is much easier to make that change, retrospectively, if it turns out that that is what the users (at some future date!) actually decide they want.