问SQL Server字符串搜索和计算

EN

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

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

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,'.')

0205991001

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',' ')

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l

GO

-- Create some data.
declare @a table(String nvarchar(100));
insert into @a values
 ('1.2.30.4.50')
,('06.7.80.90.10')
,('01.20.3.40.5');

-- This is how the function returns the data.
select Item
        ,right('0' + cast(Item as varchar(10)),2) as PadItem
from dbo.DelimitedSplit8K('1.2.30.4.50','.');

-- This is how you use FOR XML to concatenate the rows back together.
select (select '' + right('0' + cast(Item as varchar(10)),2)
        from dbo.DelimitedSplit8K(a.String,'.')
        for xml path('')
        ) as Result
from @a a;

Result
----------
0102300450
0607809010
0120034005