Inverse only numeric parts of a string in sql server

Question

With T-SQL, I am trying to find the easiest way to reverse the number in a string. therefore, for a string such as Test123Hello , there is Test321Hello .

 [Before] [After] Test123Hello Test321Hello Tt143 Hello Tt341 Hello 12Hll 21Hll Tt123H3451end Tt321H1543end 

Answer 1

Just use PATINDEX to search, add part of the result in parts:

 CREATE FUNCTION [dbo].[fn_ReverseDigits] ( @Value nvarchar(max) ) RETURNS NVARCHAR(max) AS BEGIN IF @Value IS NULL RETURN NULL DECLARE @TextIndex int = PATINDEX('%[^0-9]%', @Value), @NumIndex int = PATINDEX('%[0-9]%', @Value), @ResultValue nvarchar(max) = '' WHILE LEN(@ResultValue) < LEN(@Value) BEGIN -- Set the index to end of the string if the index is 0 SELECT @TextIndex = CASE WHEN @TextIndex = 0 THEN LEN(@Value) + 1 ELSE LEN(@ResultValue) + @TextIndex END SELECT @NumIndex = CASE WHEN @NumIndex = 0 THEN LEN(@Value) + 1 ELSE LEN(@ResultValue) + @NumIndex END IF @NumIndex < @TextIndex SELECT @ResultValue = @ResultValue + REVERSE(SUBSTRING(@Value, @NumIndex, @TextIndex -@NumIndex )) ELSE SELECT @ResultValue = @ResultValue + (SUBSTRING(@Value, @TextIndex, @NumIndex - @TextIndex)) -- Update index variables SELECT @TextIndex = PATINDEX('%[^0-9]%', SUBSTRING(@Value, LEN(@ResultValue) + 1, LEN(@Value) - LEN(@ResultValue))), @NumIndex = PATINDEX('%[0-9]%', SUBSTRING(@Value, LEN(@ResultValue) + 1, LEN(@Value) - LEN(@ResultValue))) END RETURN @ResultValue END 

SQL testing

 declare @Values table (Value varchar(20)) INSERT @Values VALUES ('Test123Hello'), ('Tt143 Hello'), ('12Hll'), ('Tt123H3451end'), (''), (NULL) SELECT Value, dbo.fn_ReverseDigits(Value) ReversedValue FROM @Values 

Result

 Value ReversedValue -------------------- -------------------- Test123Hello Test321Hello Tt143 Hello Tt341 Hello 12Hll 21Hll Tt123H3451end Tt321H1543end NULL NULL 

Answer 2

this function can be used

  CREATE FUNCTION [dbo].[fn_ReverseDigit_MA] ( @Str_IN nVARCHAR(max) ) RETURNS NVARCHAR(max) AS BEGIN DECLARE @lenstr AS INT =LEN(@Str_IN) DECLARE @lastdigend AS INT=0 while (@lastdigend<@lenstr) BEGIN DECLARE @strPart1 AS NVARCHAR(MAX)=LEFT(@Str_IN,@lastdigend) declare @lenstrPart1 AS INT=LEN(@strPart1) DECLARE @strPart2 AS NVARCHAR(MAX)=RIGHT(@Str_IN,@ lenstr-@lastdigend ) declare @digidx as int=patindex(N'%[0-9]%' ,@strPart2) +@lenstrPart1 IF(@ digidx=@lenstrPart1 ) BEGIN BREAK; END DECLARE @strStartdig AS NVARCHAR(MAX) = RIGHT(@Str_IN,@ lenstr-@digidx +1) declare @NDidx as int=patindex(N'%[^0-9]%' ,@strStartdig) +@digidx-1 IF(@NDidx< =@digidx ) BEGIN SET @ NDidx=@lenstr +1 END DECLARE @strRet AS NVARCHAR(MAX)=LEFT(@Str_IN,@digidx-1) +REVERSE(SUBSTRING(@Str_IN,@digidx,@ NDidx-@digidx )) +RIGHT(@Str_IN,@ lenstr-@NDidx +1) SET @ Str_IN=@strRet SET @ lastdigend=@NDidx-1 END return @Str_IN END 

Answer 3

hope this help:

 declare @s nvarchar(128) ='Test321Hello' declare @numStart as int, @numEnd as int select @numStart =patindex('%[0-9]%',@s) select @numEnd=len(@s)-patindex('%[0-9]%',REVERSE(@s)) select SUBSTRING(@s,0,@numstart)+ reverse(SUBSTRING(@s,@numstart,@ numend-@numstart +2))+ SUBSTRING(@s,@numend+2,len(@s) -@numend ) 

Answer 4

With this function, it will handle plural numbers.

 create FUNCTION [dbo].[GetReverseNumberFromString] (@String VARCHAR(2000)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) declare @returnstring varchar(max) =@String ; SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String, @Count, 1) >= '0' AND SUBSTRING(@String, @Count, 1) <= '9' BEGIN SET @IntNumbers = @IntNumbers + SUBSTRING(@String, @Count, 1) END IF ( SUBSTRING(@String, @Count + 1, 1) < '0' OR SUBSTRING(@String, @Count + 1, 1) > '9' ) AND SUBSTRING(@String, @Count, 1) >= '0' AND SUBSTRING(@String, @Count, 1) <= '9' BEGIN SET @IntNumbers = @IntNumbers + ',' END SET @Count = @Count + 1 END declare @RevStrings table (itemz varchar(50)) INSERT INTO @RevStrings(itemz) select items from dbo.Split(@IntNumbers,',') select @returnstring = Replace(@returnstring, itemz,REVERSE(itemz))from @RevStrings RETURN @returnstring END 

your sample string

 select [dbo].[GetReverseNumberFromString]('Tt123H3451end') 

result

 Tt321H1543end 

UPDATE:

if you don’t have a Split function, first create it I have included it below

 create FUNCTION Split ( @Input NVARCHAR(MAX), @Character CHAR(1) ) RETURNS @Output TABLE ( Items NVARCHAR(1000) ) AS BEGIN DECLARE @StartIndex INT, @EndIndex INT SET @StartIndex = 1 IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character BEGIN SET @Input = @Input + @Character END WHILE CHARINDEX(@Character, @Input) > 0 BEGIN SET @EndIndex = CHARINDEX(@Character, @Input) INSERT INTO @Output(Items) SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1) SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input)) END RETURN END GO 

Answer 5

This is a set-based approach:

 ;WITH Tally (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n) CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) ), UnpivotCTE AS ( SELECT id, xc, n, y.isNumber, n - ROW_NUMBER() OVER (PARTITION BY id, y.isNumber ORDER BY n) AS grp FROM mytable CROSS JOIN Tally CROSS APPLY (SELECT SUBSTRING(col, n, 1)) AS x(c) CROSS APPLY (SELECT ISNUMERIC(xc)) AS y(isNumber) WHERE n <= LEN(col) ), ToConcatCTE AS ( SELECT id, c, n, isNumber, grp + MIN(n) OVER (PARTITION BY id, isNumber, grp) AS grpAsc FROM UnpivotCTE ) SELECT id, col, REPLACE( (SELECT c AS [text()] FROM ToConcatCTE AS t WHERE t.id = m.id ORDER BY id, grpAsc, CASE WHEN isNumber = 0 THEN n END, CASE WHEN isNumber = 1 THEN n END DESC FOR XML PATH('')), '&#x20;',' ') AS col2 FROM mytable AS m 

A table of tables is used to "disable" all characters in a string. ROW_NUMBER is then used to identify islands of numeric and non-numeric characters. Finally, the FOR XML PATH used to restore the original string with the changed numeric islands: ORDER BY used to sort the islands of numeric characters in reverse order.

Script Demo here

Answer 6

This will make the specific line you request:

 select substring('Test123Hello',1,4) + reverse(substring('Test123Hello',5,3)) + substring('Test123Hello',8,5) 

Judging by the rest of the values, it looks like you will need to create patterns for any of the alphanumeric patterns that you get. For example, you would apply this above to any values ​​having the form:

 select * from [B&A] where [before] like '[az][az][az][az][0-9][0-9][0-9] [az][az][az][az][az]' 

In other words, if you put the values ​​(before and after) in the table [B & A] and name the columns "before" and "after", then run this:

 select substring(before,1,4) + reverse(substring(before,5,3)) + substring(before,8,5) as [after] from [B&A] where [before] like '[az][az][az][az][0-9][0-9][0-9][az] [az][az][az][az]' 

Then it will give you "Test321Hello".

However, the remaining 3 rows will not be affected unless you create a similar '[0-9] [az]' for each alphanumeric form and apply this to the [B & A] table. You will need to select the results in the temp table or another table.

Applying each template in turn, you will get most of it, then you will need to see how many lines have not been affected, and check what the alphanumeric form is, and make more templates. In the end, you have a set of code that, if you run it, will capture all possible combinations.

You could just sit down and create code in a way that captures all possible combinations of [az] and [0-9]. Much depends on the maximum number of characters you deal with.