InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:38:37 GMT20http://www.sqlservercentral.com/Forums/Topic1353709-8-1.aspxPerhaps something like this:[code="sql"]DECLARE @t TABLE (ID INT IDENTITY, strcol VARCHAR(20))INSERT INTO @tSELECT 'aa001' UNION ALL SELECT 'ab001'UNION ALL SELECT 'aa001a0' UNION ALL SELECT 'aa001b0';WITH Tally (n) AS ( SELECT TOP 999 RIGHT('00' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR), 3) FROM sys.all_columns)SELECT STUFF(strcol, m, 3, n) FROM @tCROSS APPLY (SELECT CHARINDEX('001', strcol)) a(m)CROSS APPLY TallyORDER BY ID, n[/code]Thu, 06 Sep 2012 23:50:10 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1353709-8-1.aspxThank but only one value inserting in a table it's not incremented.example in table only aa001 only displaying it should be incremented till aa999 na.Wed, 05 Sep 2012 00:05:36 GMTvenki.msghttp://www.sqlservercentral.com/Forums/Topic1353709-8-1.aspxif you can have an identity column in your table, you can create a calculated persisted column that auto generates that texty-like value.[code]--#################################################################################################--Pattern: AA000 to ZZ999 max value=676000--#################################################################################################IF OBJECT_ID('X') IS NOT NULL DROP TABLE XCREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 676000)), --limit=26 * 26 + 999 + 1XCALCULATED AS CHAR((XID/26000)%26+65) --1st Letter +CHAR((XID/1000)%26+65) --2nd Letter +REPLACE(STR(XID%1000,3),' ','0') PERSISTED, --The 3 digit numeric partSOMEOTHERCOL VARCHAR(30))INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')SET IDENTITY_INSERT X ON INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')SET IDENTITY_INSERT X OFFSET IDENTITY_INSERT X ON INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF') --FAILS! too big!SET IDENTITY_INSERT X OFFSELECT * FROM X--three char table: bigger range--#################################################################################################--Pattern: AAA000 to ZZZ999 max value=196040000--#################################################################################################IF OBJECT_ID('X') IS NOT NULL DROP TABLE XCREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 196040000)),XCALCULATED AS CHAR((XID/260000)%26+65) --1st Letter + CHAR((XID/26000)%26+65) --2nd Letter + CHAR((XID/1000)%26+65) --3rd Letter + REPLACE(STR(XID%10000,4),' ','0') PERSISTED, --The 4 digit numeric partSOMEOTHERCOL VARCHAR(30))INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')SET IDENTITY_INSERT X ON INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')SET IDENTITY_INSERT X OFFSET IDENTITY_INSERT X ON INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')SET IDENTITY_INSERT X OFFSELECT * FROM XXID XCALCULATED SOMEOTHERCOL 1 AA001 WHATEVER 675999 ZZ999 MORESTUFF [/code]Tue, 04 Sep 2012 06:52:22 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1353709-8-1.aspxthank you:-)Tue, 04 Sep 2012 06:47:47 GMTvenki.msghttp://www.sqlservercentral.com/Forums/Topic1353709-8-1.aspx[quote][b]venki.msg (9/4/2012)[/b][hr]I need logic for generating following pattern.AA001A0 TO AA999A0 THIS IS FIRST SCENARIO.NEXT IS AA001B0 TO AA999B0 IS SECOND SCENARIO.[/quote]If you have only these two fix scenario.Then you can try Jeff's Tally table as mentioned below.[code="sql"]--===== Do this in a nice safe place that everyone has -- (You can build a permanent one in any database) USE TempDB; IF OBJECT_ID('TempDB..Tally','U') IS NOT NULL DROP TABLE Tally;GO--===================================================================-- Create a Tally table from 1 to 1000 (you can increase this number as per your requirement)--===================================================================--===== Create and populate the Tally table on the fly. SELECT TOP 1000 IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.sys.ALL_Columns ac1 CROSS JOIN Master.sys.ALL_Columns ac2;--===== Add a CLUSTERED Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100;--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC;GO------ Now move to your query SELECT 'AA' + RIGHT('00'+ CONVERT(VARCHAR,N),3) + 'A0' AS NUM FROM tempdb.dbo.TallyWHERE N < 1000[/code]Likewise you can try out the second pattern on your own :-)Tue, 04 Sep 2012 04:42:54 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1353709-8-1.aspxI need logic for generating following pattern.AA001A0 TO AA999A0 THIS IS FIRST SCENARIO.NEXT IS AA001B0 TO AA999B0 IS SECOND SCENARIO.Tue, 04 Sep 2012 03:34:33 GMTvenki.msghttp://www.sqlservercentral.com/Forums/Topic1353709-8-1.aspx[quote][b]venki.msg (9/3/2012)[/b][hr]Hi all i need to do increment a number like aa001 to aa999 then again ab001 to ab999aa001a0 to aa999a0 then aa001b0 to aa999b0.Can any one help me out to find logic for this.[/quote]Sorry I am confused with your examples [quote]aa001 to aa999 then again ab001 to ab999aa001a0 to aa999a0 then aa001b0 to aa999b0[/quote]Could you please explain your exact requirement with clear expected output.Tue, 04 Sep 2012 00:10:14 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1353709-8-1.aspxHi all i need to do increment a number like aa001 to aa999 then again ab001 to ab999aa001a0 to aa999a0 then aa001b0 to aa999b0.Can any one help me out to find logic for this.Mon, 03 Sep 2012 23:23:51 GMTvenki.msg