September 3, 2012 at 11:23 pm
Hi all i need to do increment a number like aa001 to aa999 then again ab001 to ab999
aa001a0 to aa999a0 then aa001b0 to aa999b0.
Can any one help me out to find logic for this.
September 4, 2012 at 12:10 am
venki.msg (9/3/2012)
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.
Sorry I am confused with your examples
aa001 to aa999 then again ab001 to ab999
aa001a0 to aa999a0 then aa001b0 to aa999b0
Could you please explain your exact requirement with clear expected output.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 4, 2012 at 3:34 am
I need logic for generating following pattern.
AA001A0 TO AA999A0 THIS IS FIRST SCENARIO.
NEXT IS AA001B0 TO AA999B0 IS SECOND SCENARIO.
September 4, 2012 at 4:42 am
venki.msg (9/4/2012)
I need logic for generating following pattern.AA001A0 TO AA999A0 THIS IS FIRST SCENARIO.
NEXT IS AA001B0 TO AA999B0 IS SECOND SCENARIO.
If you have only these two fix scenario.Then you can try Jeff's Tally table as mentioned below.
--===== 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.Tally
WHERE N < 1000
Likewise you can try out the second pattern on your own 🙂
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 4, 2012 at 6:47 am
thank you:-)
September 4, 2012 at 6:52 am
if you can have an identity column in your table, you can create a calculated persisted column that auto generates that texty-like value.
--#################################################################################################
--Pattern: AA000 to ZZ999 max value=676000
--#################################################################################################
IF OBJECT_ID('X') IS NOT NULL
DROP TABLE X
CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 676000)), --limit=26 * 26 + 999 + 1
XCALCULATED 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 part
SOMEOTHERCOL 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 OFF
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF') --FAILS! too big!
SET IDENTITY_INSERT X OFF
SELECT * FROM X
--three char table: bigger range
--#################################################################################################
--Pattern: AAA000 to ZZZ999 max value=196040000
--#################################################################################################
IF OBJECT_ID('X') IS NOT NULL
DROP TABLE X
CREATE 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 part
SOMEOTHERCOL 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 OFF
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SELECT * FROM X
XID XCALCULATED SOMEOTHERCOL
1 AA001 WHATEVER
675999 ZZ999 MORESTUFF
Lowell
September 5, 2012 at 12:05 am
Thank 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.
September 6, 2012 at 11:50 pm
Perhaps something like this:
DECLARE @t TABLE (ID INT IDENTITY, strcol VARCHAR(20))
INSERT INTO @t
SELECT '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 @t
CROSS APPLY (SELECT CHARINDEX('001', strcol)) a(m)
CROSS APPLY Tally
ORDER BY ID, n
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply