With T-SQL, I'm trying to find the easiest way to reverse numbers in string. so for string like Test123Hello have Test321Hello.
[Before] [After]
Test123Hello Test321Hello
Tt143 Hello Tt341 Hello
12Hll 21Hll
Tt123H3451end Tt321H1543end
you can use this function
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
Just make use of PATINDEX for searching, append to the result string part by part:
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
Test SQL
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
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)
Use this function it will handle multiple occurrence of numbers too
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 do not have Split function then 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
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, x.c, 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(x.c)) 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('')), ' ',' ') AS col2
FROM mytable AS m
A tally table is used in order to 'unpivot' all characters of the string. Then ROW_NUMBER is used in order to identify islands of numeric and non-numeric characters. Finally, FOR XML PATH is used to reconstruct the initial string with numerical islands reversed: ORDER BY is used to sort islands of numeric characters in reversed order.
This would do the specific string you are asking for:
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 would need to make templates for any of the alphanumeric patterns you are getting. For example you would apply the above to any values that had the shape:
select * from [B&A] where [before] like '[a-z][a-z][a-z][a-z][0-9][0-9][0-9]
[a-z][a-z][a-z][a-z][a-z]'
In other words, if you put the values (before and after) into a table [B&A] and called the columns 'before' and 'after' then ran this:
select
substring(before,1,4)
+
reverse(substring(before,5,3))
+
substring(before,8,5) as [after]
from [B&A] where [before] like '[a-z][a-z][a-z][a-z][0-9][0-9][0-9][a-z]
[a-z][a-z][a-z][a-z]'
Then it would give you 'Test321Hello'.
However the other 3 rows would not be affected unless you created a similar '[0-9][a-z]' type template for each alphanumeric shape and applied this to the [B&A] table. You would have to select the results into a temp table or another table.
By applying each template in turn you'd get most of it then you'd have to see how many rows were unaffected and check what the alphanumeric shape is and make more templates. Eventually you have a set of code which, if you ran it would capture all possible combinations.
You could just sit down and design a code in this way which captured all possible combinations of [a-z] and [0-9]. A lot depends on the maximum number of characters you are dealing with.
