Reverse only numerical parts of string in sql server

Question

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

Viv

Is this just for fun, or a silly assignment, or a real work issue? – Needlepoint

The numbers part are reversed, and the need to restore in my data – Viv

A lot depends on the parameters around the string. Are there always words before and after the numbers and are the words of the same or differing lengths? – Visional

Can it be any number of digits, in any position? Can it be two (or more) numbers in the same string? – Needlepoint

i eplained with examples – Viv

@Viv you would be better off not making T-SQL a requirement and just writing a small script to do the work instead. T-SQL is not designed for string manipulation so a universal solution will be needlessly complex unless you need to do this operation many times (even then you could look at doing it with a CLR extension instead) – Baxy

check my answer and et me know if u have still problmes – California

Answer 1

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

Robbegrillet

Answer 2

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

Xenocryst

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)

Congest

it is work for strings that have only one numberical sequence – Congest

with this example your answer don't work "Tt123H3451end" – Viv

Answer 4

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

California

try this and let me know if still you have problems – California

Invalid object name 'dbo.Split'. – Viv

ok so you do not have split function wait i can include it too – California

create Split function and then try . let me know if u got any error again – California

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, 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('')), '&#x20;',' ') 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.

Fiddle Demo here

Megalocardia

Answer 6

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.

Visional

At the end you will end up with distinct patters for every row. I suspect OP wants universal solution – Nicolis

I would agree looking at the four rows given it would take four different patterns. I don't know that the data he's getting is always different so I thought gradually each pattern would recur and so there would not need to be a pattern per row. A universal solution would certainly be nice. Yup. – Visional