How to convert 119.305.1983984.1 to 1.1983984.305.119 in sql server?

user_10877817 0
Viewed 106

I have to convert 119.305.1983984.1 to 1.1983984.305.119 in SQL Server.

I have tried as shown below using reverse, but it does not work

DECLARE @myvar varchar(20);  
SET @myvar = '119.305.1983984.1';  
SELECT REVERSE(@myvar) AS Reversed ;  
GO

The result is 1.4893891.503.911
Expected result is 1.1983984.305.119

I do not want to be reversed.just want last to first showed: 1.1983984.305.119.

sql-server-2016 tsql sql-server sql
4 Answers
DECLARE @myvar varchar(17) = '119.305.1983984.1'; --It's 17 not 10

WITH CTE AS
(
SELECT Value, 
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN
FROM STRING_SPLIT(@myvar, '.')
)
SELECT STRING_AGG(Value, '.') WITHIN GROUP (ORDER BY RN DESC)
FROM CTE;

Returns:

+-------------------+
| 1.1983984.305.119 |
+-------------------+
user_64266920

If number of tokens is known and it is up to four (SQL Server 2012+):

DECLARE @myvar varchar(20);  
SET @myvar = '119.305.1983984.1';  

SELECT CONCAT(PARSENAME(@myvar,1),'.',PARSENAME(@myvar,2),'.',PARSENAME(@myvar,3),'.',PARSENAME(@myvar,4))

Original value: 119.305.1983984.1

SELECT returns: 1.1983984.305.119

If number of tokens is varying and SQL Server is 2017+ :

DECLARE @myvar varchar(20), @separator CHAR(1)  
SET @myvar = '119.305.1983984.1';  
SET @separator = '.'


SELECT STRING_AGG(value, @separator)  WITHIN GROUP ( ORDER BY rn DESC ) 
FROM (
    SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT 0) ) rn
    FROM STRING_SPLIT ( @myvar , @separator )  
) d OPTION (MAXDOP 1)

SELECT returns: 1.1983984.305.119

user_50216870

You can try the following query as shown below. This will run any versions of Sql server and no need to pass hard value like 1, 2 or n for position.

Declare @test varchar(max)
Set @test = '119.305.1983984.1'

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+'.' ,'') + Convert(Varchar(10), IpAddress)
from (
Select 
   ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN,  T2.item.value('(./text())[1]','int') as IpAddress
from
     (select convert(xml,'<items><t>'+replace(@test,'.','</t><t>')+'</t></items>') as xmldoc)
as xmltable
     CROSS APPLY xmltable.xmldoc.nodes('/items/t') as T2(item)      
)a order by RN desc

select @listStr
user_105325000

You can create a split function through a Delimiter (.). Then all values turn into a table. By ROW_NUMBER() function you can define a number along with all items to REVERSE them. Finally, you can use COALESCE to concatenate them.

split function

CREATE FUNCTION [dbo].[Split](@Delimiter varchar(5), @List varchar(8000) ) 
RETURNS @TableOfValues table 
(  Value varchar(50)   ) 
AS 
BEGIN

DECLARE @LenString int 

WHILE len( @List ) > 0 
    BEGIN 

    SELECT @LenString = 
        (CASE charindex( @Delimiter, @List ) 
            WHEN 0 THEN len( @List ) 
            ELSE ( charindex( @Delimiter, @List ) -1 )
        END
        ) 

    INSERT INTO @TableOfValues 
        SELECT substring( @List, 1, @LenString )

    SELECT @List = 
        (CASE ( len( @List ) - @LenString ) 
            WHEN 0 THEN '' 
            ELSE right( @List, len( @List ) - @LenString - 1 ) 
        END
        ) 
    END
    RETURN 
END

OUTPUT Query

DECLARE @myvar varchar(20);  
SET @myvar = '119.305.1983984.1';
Declare @val Varchar(MAX);
Select @val = COALESCE(@val + '.' + Value, Value) 
FROM 
    (
        SELECT Value,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as number from [dbo].[Split]('.',@myvar)
    ) AS T ORDER BY number DESC
SELECT @val;

OUTPUT 1.1983984.305.119

user_72505360
Related