Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Increment in sql server. Expand / Collapse
Author
Message
Posted Monday, September 3, 2012 11:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 12:02 PM
Points: 5, Visits: 16
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.
Post #1353709
Posted Tuesday, September 4, 2012 12:10 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 559, Visits: 884
venki.msg (9/3/2012)
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.


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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1353721
Posted Tuesday, September 4, 2012 3:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 12:02 PM
Points: 5, Visits: 16
I need logic for generating following pattern.

AA001A0 TO AA999A0 THIS IS FIRST SCENARIO.

NEXT IS AA001B0 TO AA999B0 IS SECOND SCENARIO.
Post #1353784
Posted Tuesday, September 4, 2012 4:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 559, Visits: 884
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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1353800
Posted Tuesday, September 4, 2012 6:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 12:02 PM
Points: 5, Visits: 16
thank you
Post #1353857
Posted Tuesday, September 4, 2012 6:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1353861
Posted Wednesday, September 5, 2012 12:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 12:02 PM
Points: 5, Visits: 16
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.
Post #1354332
Posted Thursday, September 6, 2012 11:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:12 PM
Points: 3,615, Visits: 5,229
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1355759
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse