CODEWITHSUNDEEP
sqlsql-serverstringt-sqlincrement
igmox igmox
igmox igmox

Reputation: 51

sql extract rightmost number in string and increment

i have transaction codes like 

 "A0004", "1B2005","20CCCCCCC21"

I need to extract the rightmost number and increment the transaction code by one

"AA0004"----->"AA0005"
"1B2005"------->"1B2006"
"20CCCCCCCC21"------>"20CCCCCCCC22"

in SQL Server 2012.

dealing with unsignificant number of string and number length is out of my league. some logic is always missing.

LEFT(@a,2)+RIGHT('000'+CONVERT(NVARCHAR,CONVERT(INT,SUBSTRING( SUBSTRING(@a,2,4),2,3))+1)),3

Upvotes: 1

Views: 1266

Answers (3)

igmox igmox
igmox igmox

Reputation: 51

STUFF(@X ,LEN(@X)-CASE PATINDEX('%[A-Z]%',REVERSE(@X)) WHEN 0 THEN LEN(@X) ELSE PATINDEX('%[A-Z]%',REVERSE(@X))-1 END+1 ,LEN(((RIGHT(@X,CASE PATINDEX('%[A-Z]%',REVERSE(@X)) WHEN 0 THEN LEN(@X) ELSE PATINDEX('%[A-Z]%',REVERSE(@X))-1 END)/@N)+1)@N) ,((RIGHT(@X,CASE PATINDEX('%[A-Z]%',REVERSE(@X)) WHEN 0 THEN LEN(@X) ELSE PATINDEX('%[A-Z]%',REVERSE(@X))-1 END)/@N)+1)@N)

  • works on number only strings
  • 99 becomes 100
  • mod(@N) increments

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82484

First, I want to be clear about this: I totally agree with the comments to the question from a_horse_with_no_name and Jeroen Mostert.
You should be storing one data point per column, period.

Having said that, I do realize that a lot of times the database structure can't be changed - so here's one possible way to get that calculation for you.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE 
(
    col varchar(100)
);

INSERT INTO @T (col) VALUES
('A0004'),
('1B2005'),
('1B2000'),
('1B00'),
('20CCCCCCC21');

(I've added a couple of strings as edge cases you didn't mention in the question)

Then, using a couple of cross apply to minimize code repetition, I came up with that:

SELECT  col,
        LEFT(col, LEN(col) - LastCharIndex + 1) + 
        REPLICATE('0', LEN(NumberString) - LEN(CAST(NumberString as int))) + 
        CAST((CAST(NumberString as int) + 1) as varchar(100)) As Result
FROM @T
CROSS APPLY
(
    SELECT PATINDEX('%[^0-9]%', Reverse(col)) As LastCharIndex
) As Idx
CROSS APPLY 
(
    SELECT RIGHT(col, LastCharIndex - 1) As NumberString
) As NS

Results:

col         Result
A0004       A0005
1B2005      1B2006
1B2000      1B2001
1B00        1B01
20CCCCCCC21 20CCCCCCC22

The LastCharIndex represents the index of the last non-digit char in the string. The NumberString represents the number to increment, as a string (to preserve the leading zeroes if they exists).

From there, it's simply taking the left part of the string (that is, up until the number), and concatenate it to a newly calculated number string, using Replicate to pad the result of addition with the exact number of leading zeroes the original number string had.

Upvotes: 2

Sreenu131
Sreenu131

Reputation: 2516

Try This

DECLARE @test nvarchar(1000) ='"A0004", "1B2005","20CCCCCCC21"'
DECLARE @Temp AS TABLE (ID INT IDENTITY,Data nvarchar(1000))
INSERT INTO @Temp 
SELECT @test

;WITH CTE
AS
(
SELECT Id,LTRIM(RTRIM((REPLACE(Split.a.value('.' ,' nvarchar(max)'),'"',''))))  AS Data
,RIGHT(LTRIM(RTRIM((REPLACE(Split.a.value('.' ,' nvarchar(max)'),'"','')))),1)+1 AS ReqData
FROM
(
SELECT ID,
CAST ('<S>'+REPLACE(Data,',','</S><S>')+'</S>' AS XML) AS Data
FROM @Temp
) AS A
CROSS APPLY Data.nodes ('S') AS Split(a)
)
SELECT CONCAT('"'+Data+'"','-------->','"'+CONCAT(LEFT(Data,LEN(Data)-1),CAST(ReqData AS VARCHAR))+'"') AS ExpectedResult
FROM CTE

Result

ExpectedResult
-----------------
"A0004"-------->"A0005"
"1B2005"-------->"1B2006"
"20CCCCCCC21"-------->"20CCCCCCC22"

Upvotes: 0

Related Questions