SQL Server string search and evaluate

Question

In SQL Server I want to evaluate a string like 1.2.30.4.50 and output with 0102300450. Basically evaluate each number before the period and left pad it with a zero if it has a single digit and output two digits. If the number already has two digits it should be left untouched.

A string like 2.5.99.10.1 should evaluate to 0205991001. Note there are no zeros before 99 and 10.

I know the below query will return the 1st number before the first period occurrence. But I'm unable to take this further. Any help appreciated!

If col contains a string say 1.2.30.4.50:

SELECT left(col, charindex('.', col) - 1) FROM table 

will return the below:

 column1    
 ---------- 
 1 

Answer 1

You can as the below

CREATE FUNCTION PrepareString
(    
    @val varchar(max), 
    @delimiter varchar(10)
)
RETURNS VARCHAR(MAX)
AS
BEGIN   

    DECLARE @xml as xml 
    SET @xml = cast(('<X>'+replace(@val,@delimiter ,'</X><X>')+'</X>') as xml)

    RETURN 
        (
            SELECT
                (
                    SELECT 
                         CASE WHEN LEN(N.value('.', 'varchar(MAX)')) = 1 THEN '0' + N.value('.', 'varchar(MAX)') ELSE N.value('.', 'varchar(MAX)') END 
                    FROM @xml.nodes('X') as T(N)
                    FOR XML PATH ('')
                ) A
        )
END
GO

Example:

SELECT dbo.PrepareString('2.5.99.10.1', '.') -- 0205991001
SELECT dbo.PrepareString('1.2.30.4.50', '.') -- 0102300450
SELECT dbo.PrepareString('1.2.30.4.50.123.3.443.2', '.') -- 01023004501230344302

Answer 2

My first thought was to use ParseName(), but there are too many observations

Declare @String varchar(50) = '2.5.99.10.1'
Select right('0'+Pos1,2)+right('0'+Pos2,2)+right('0'+Pos3,2)+right('0'+Pos4,2)+right('0'+Pos5,2)
 From  [dbo].[udf-Str-Parse-Row](@String,'.')

Returns

0205991001

The Row Parse UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = xDim.value('/x[1]','varchar(max)')
          ,Pos2 = xDim.value('/x[2]','varchar(max)')
          ,Pos3 = xDim.value('/x[3]','varchar(max)')
          ,Pos4 = xDim.value('/x[4]','varchar(max)')
          ,Pos5 = xDim.value('/x[5]','varchar(max)')
          ,Pos6 = xDim.value('/x[6]','varchar(max)')
          ,Pos7 = xDim.value('/x[7]','varchar(max)')
          ,Pos8 = xDim.value('/x[8]','varchar(max)')
          ,Pos9 = xDim.value('/x[9]','varchar(max)')
     From (Select Cast('<x>' + Replace(@String,@Delimiter,'</x><x>')+'</x>' as XML) as xDim) A
)
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')