tag:blogger.com,1999:blog-3682177839638728927.post6730077364506476264..comments2024-03-09T14:05:29.528+05:30Comments on SQL: Auto Generate AlphaNumeric ID’s in a SQL Server TableAnonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-3682177839638728927.post-2496127601130338462017-04-18T00:58:14.610+05:302017-04-18T00:58:14.610+05:30Nice example.. Seems to help us Newbies..
However...Nice example.. Seems to help us Newbies..<br /><br />However,i have a tricky question that sort of relates to this code..<br /><br />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<br /><br />So on an insert of a date field. The trigger or sp must spit out the sequence above..<br /><br />How would i achieve this?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-38564832449577641932015-06-01T15:59:47.305+05:302015-06-01T15:59:47.305+05:30DECLARE @TT TABLE (CandID as 'S-' + RIGHT(...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<br />OUTPUTAnonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-50816598246781023692015-05-22T14:32:05.150+05:302015-05-22T14:32:05.150+05:30In this case, when the number reaches 9.. this wil...In this case, when the number reaches 9.. this will happen -:<br /><br />S-0008<br />S-0009<br />S-00010<br />S-00011<br /><br />But, technically it should be -:<br /><br />S-0008<br />S-0009<br />S-0010<br />S-0011<br /><br />How to solve this problem?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-18973950738659350882014-07-21T10:56:00.516+05:302014-07-21T10:56:00.516+05:30CardID columns default value will be
'S-'...CardID columns default value will be <br />'S-' plus <br />CourseID : four digit numeric number prefixed by zero Anonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-65891687553193997752014-07-20T16:27:22.245+05:302014-07-20T16:27:22.245+05:30DECLARE @TT TABLE (CandID as 'S-' + RIGHT(...DECLARE @TT TABLE (CandID as 'S-' + RIGHT('000' + CONVERT(varchar, CourseID),5)<br /><br />Sir can u please explain me the meaning of this line?Ankushnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-29827685491118447182014-06-23T09:52:11.281+05:302014-06-23T09:52:11.281+05:30Hi Swati,
The data should be in proper Order.
Here...Hi Swati,<br />The data should be in proper Order.<br />Here I can see that two type of order is running in single column.<br />This can not be done.<br />Anonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-38992013181487817282014-06-14T14:39:13.505+05:302014-06-14T14:39:13.505+05:30i want to auto generate one column is voucher and ...i want to auto generate one column is voucher and in this two types generate voucher no<br />first type starting number is 1 and second type starting number is 1001 .<br />for ex:- the column is voucher and data type is nvarchar(50) and the data is : - <br />1<br />2<br />3<br />1001<br />1002<br />4<br /><br />How can i do it?Anonymoushttps://www.blogger.com/profile/16225692251409029579noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-44907298899165017772013-08-26T10:28:05.901+05:302013-08-26T10:28:05.901+05:30DECLARE @TT TABLE (CustomerID as SUBSTRING(CumerNa...DECLARE @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 @TTAnonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-68607745742373125912013-08-24T19:14:49.514+05:302013-08-24T19:14:49.514+05:30i want to auto generate 5 letter customer id...whe...i 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?Anonymoushttps://www.blogger.com/profile/06060003888613043839noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-39871064934770804902013-08-05T14:41:47.765+05:302013-08-05T14:41:47.765+05:30DECLARE @TT TABLE (CandID as 'P' + RIGHT(C...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 @TTAnonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-53830387000511304972013-07-23T10:46:25.491+05:302013-07-23T10:46:25.491+05:30i want to set my columns value as
p101
p102
p103
...i want to set my columns value as <br />p101<br />p102<br />p103<br /><br /> help pleaseAnonymoushttps://www.blogger.com/profile/08822336855358555350noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-1626593834827509752011-07-19T11:32:09.887+05:302011-07-19T11:32:09.887+05:30Use the below query to generate above result:
DEC...Use the below query to generate above result:<br /><br />DECLARE @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 @TTAnonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-11797179590491022202011-07-18T14:28:36.919+05:302011-07-18T14:28:36.919+05:30this code is nice can you able to do like this
i...this code is nice can you able to do like this <br /><br />i want to update one column as <br />101C_1<br />101C_2<br />101C_3<br /><br />How can i do it?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-62678997373047318232011-04-19T18:09:59.999+05:302011-04-19T18:09:59.999+05:30Thanks for the suggestion.
Sure, I will change th...Thanks for the suggestion.<br /><br />Sure, I will change the color scheme.Anonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.com