SQL Computed Column

anuspc
anuspc used Ask the Experts™
on
I am trying to generate a unique column CardControlNumber. I want to have some string or suffix attached to a auto number. For example my CardControl Number should look some thing like this:
AMSN0000001
AMSN0000002...
If i cannot suffix with string, i can also suffix my suto number with IDs like
11000000001
11000000002

How can i do this in SQL??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You cannot do things like that with auto-number column in SQL.
I suggest you to use another field for storing prefix code (like AMSN or 11,...)
To display, you can use the first query.

If you want the auto-number field runs with each code separately, do not use identity field. Just use a normal and add a trigger to create auto-number value when new row is added.
The second query may be useful for you.

P/S : I write this in SQL syntax but you can use it in MySQL with some minor changes.


QUERY 1 : 

SELECT PrefixCode + (CASE WHEN AutoNumber > 999999999 THEN Convert(varchar(20),AutoNumber) ELSE RIGHT("000000000" + Convert(varchar(20),AutoNumber), 9) END) as myCode, T.*
FROM YourTable T

QUERY 2 :

CREATE TRIGGER CreateAutoNumber
   ON  YourTable
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @id int, @prefix varchar(100)
	
	DECLARE InsertCursor CURSOR FOR SELECT Id, PrefixCode FROM inserted
	OPEN InsertCursor
	FETCH NEXT FROM InsertCursor INTO @id, @prefix
	WHILE (@@FETCH_STATUS==0)
	BEGIN
		UPDATE YourTable
		SET AutoNumber = T.AutoNumber
		FROM (
			SELECT MAX(isNull(AutoNumber,0))+1 AutoNumber FROM YourTable WHERE PrefixCode = @prefix
		) T
		WHERE Id = @id
		FETCH NEXT FROM InsertCursor INTO @id, @prefix
	END
	CLOSE InsertCursor
	DEALLOCATE InsertCursor
END
GO

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial