If you wants to auto generate a Column ID based on an IDENTITY column, then here’s the query.
DECLARE @TT TABLE (CandID as 'S-' + RIGHT('000' + CONVERT(varchar, CourseID),5),CourseID int IDENTITY(1,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TTOUTPUT
SlNo | CardID | CourseID | ReferenceName |
1 | S-0001 | 1 | Nile |
2 | S-0002 | 2 | Mani |
3 | S-0003 | 3 | Aja |
4 | S-0004 | 4 | Nile |
Thanks for the suggestion.
ReplyDeleteSure, I will change the color scheme.
this code is nice can you able to do like this
ReplyDeletei want to update one column as
101C_1
101C_2
101C_3
How can i do it?
Use the below query to generate above result:
ReplyDeleteDECLARE @TT TABLE (CandID as '101C_' + RIGHT(CONVERT(varchar, CourseID),5),CourseID int IDENTITY(1,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT
i want to set my columns value as
ReplyDeletep101
p102
p103
help please
DECLARE @TT TABLE (CandID as 'P' + RIGHT(CONVERT(varchar, CourseID),5),CourseID int IDENTITY(101,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT
Deletei want to auto generate 5 letter customer id...where the first letter should be the first letter of the first name of the customer and the other 4 letters should be consisted of the characters,symbols and digits ....how to do it?
ReplyDeleteDECLARE @TT TABLE (CustomerID as SUBSTRING(CumerName, 1, 1) + RIGHT(CONVERT(varchar, CardID),5), CardID int IDENTITY(1001,1),CumerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT
Deletei want to auto generate one column is voucher and in this two types generate voucher no
ReplyDeletefirst type starting number is 1 and second type starting number is 1001 .
for ex:- the column is voucher and data type is nvarchar(50) and the data is : -
1
2
3
1001
1002
4
How can i do it?
Hi Swati,
DeleteThe data should be in proper Order.
Here I can see that two type of order is running in single column.
This can not be done.
DECLARE @TT TABLE (CandID as 'S-' + RIGHT('000' + CONVERT(varchar, CourseID),5)
ReplyDeleteSir can u please explain me the meaning of this line?
CardID columns default value will be
Delete'S-' plus
CourseID : four digit numeric number prefixed by zero
In this case, when the number reaches 9.. this will happen -:
ReplyDeleteS-0008
S-0009
S-00010
S-00011
But, technically it should be -:
S-0008
S-0009
S-0010
S-0011
How to solve this problem?
DECLARE @TT TABLE (CandID as 'S-' + RIGHT((CASE WHEN LEN(CONVERT(varchar, CourseID)) = 1 THEN '000' WHEN LEN(CONVERT(varchar, CourseID)) = 2 THEN '00' WHEN LEN(CONVERT(varchar, CourseID)) = 3 THEN '0' ELSE '' END) + CONVERT(varchar, CourseID),5),CourseID int IDENTITY(1,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT
DeleteOUTPUT
Nice example.. Seems to help us Newbies..
ReplyDeleteHowever,i have a tricky question that sort of relates to this code..
I have to generate alphanumeric numbers in a format like this: AC/17/Ass001 or AC/17/Mod001. The 17 indicates the year and the Ass or Mod indicates the course that the person does.. So if its Ass the course will be Assessor etc
So on an insert of a date field. The trigger or sp must spit out the sequence above..
How would i achieve this?