SQL Server-拆分列数据并检索最后一秒的值

使用split string函数,但不要使用内置函数,因为它只返回值,并且会丢失位置数据。

你可以用杰夫·摩登的 DelimitedSplit8K

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
;

然后可以使用它拆分字符串,它将返回如下表:

DECLARE @string varchar(100) = '.105248.105250.104150.111004.';

SELECT *
FROM [dbo].[DelimitedSplit8K](@string, '.')

ItemNumber  Item
1   
2           105248
3           105250
4           104150
5           111004
6   

您只需要实际有一个项的部分,所以添加一个where子句,您需要最后一个so add中的第二个 row_number() ,并希望将整个内容放在公共表表达式中,以便可以查询:

DECLARE @string varchar(100) = '.105248.105250.104150.111004.';

WITH CTE AS
(
    SELECT Item, ROW_NUMBER() OVER(ORDER BY ItemNumber DESC) As rn
    FROM [dbo].[DelimitedSplit8K](@string, '.')
    WHERE Item <> ''
)

SELECT Item
FROM CTE
WHERE rn = 2

结果: 104150

如果总是有四个部分,你可以使用 PARSENAME() :

DECLARE @s varchar(64) = '.105248.105250.104150.111004.';

SELECT PARSENAME(SUBSTRING(@s, 2, LEN(@s)-2),2);

根据您的SQL SERVER版本,还可以使用 STRING_SPLIT 功能。

DECLARE @string varchar(100) = '.105248.105250.104150.111004.';

SELECT  value,
        ROW_NUMBER() OVER (ORDER BY CHARINDEX('.' + value + '.', '.' + @string + '.')) AS Pos
FROM    STRING_SPLIT(@string,'.')  
WHERE   RTRIM(value) <> '';

它不像Jeff的splitter那样返回原来的位置,但是如果你查看Aaron Bertrand的文章,它会比较有利:

Performance Surprises and Assumptions : STRING_SPLIT()

编辑 :

您可以使用参数stringvalue和delemeter创建一个sqlserver表值函数,并按预期为结果调用该函数。

ALTER function [dbo].[SplitString] 
(
@str nvarchar(4000), 
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select 
1, 
1, 
charindex(@separator, @str)
union all
select
p + 1, 
b + 1, 
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 ID,
substring(
@str, 
a, 
case when b > 0 then b-a ELSE 4000 end) 
AS s
from tokens
)

调用函数

SELECT * FROM [DBO].[SPLITSTRING] ('.105248.105250.104150.111004.', '.') WHERE ISNULL(S,'') <> ''

ID  s
1   105248
2   105250
3   104150
4   111004

要只获得第二个值,您可以编写查询,如下所示

DECLARE @MaxID INT
SELECT @MaxID = MAX (ID) FROM (SELECT * FROM [DBO].[SPLITSTRING] ('.105248.105250.104150.111004.', '.') WHERE ISNULL(S,'') <> '') A

SELECT TOP 1 @MaxID =  MAX (ID) FROM (
SELECT * FROM [DBO].[SPLITSTRING] ('.105248.105250.104150.111004.', '.') WHERE ISNULL(S,'') <> ''
)a where ID < @MaxID

SELECT * FROM [DBO].[SPLITSTRING] ('.105248.105250.104150.111004.', '.') WHERE ISNULL(S,'') <> '' AND ID = @MaxID

ID  s
3   104150

如果您想要1作为ID的值,那么您可以编写查询,如下查询的最后一行所示。

SELECT 1 AS ID , S FROM [DBO].[SPLITSTRING] ('.105248.105250.104150.111004.', '.') WHERE ISNULL(S,'') <> '' AND ID = @MaxID

ID  S
1   104150

试试这个

DECLARE @DATA AS TABLE (Data nvarchar(1000))
INSERT INTO @DATA
SELECT '.105248.105250.104150.111004.'
;WITH CTE
AS
(
SELECT Data,ROW_NUMBER()OVER(ORDER BY Data DESC) AS Rnk
FROM
(
    SELECT Split.a.value('.','nvarchar(100)') Data
    FROM(
    SELECT CAST('<S>'+REPLACE(Data,'.','</S><S>')+'</S>' AS XML ) As Data
    FROM @DATA
)DT 
CROSS APPLY Data.nodes('S') AS Split(a)
) AS Fnl
WHERE Fnl.Data <>''

)
SELECT Data FROM CTE
WHERE Rnk=2

结果

Data
-----
105248
105250
104150
111004

也只能使用字符串函数实现:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

SELECT '.105248.105250.104150.111004.' code INTO #temp UNION ALL 
SELECT '.205248.205250.204150.211004.' 

SELECT 
REVERSE(LEFT(
            REVERSE(LEFT(code,  LEN(code) - CHARINDEX('.', REVERSE(code), 2)))
            ,  CHARINDEX('.',REVERSE(LEFT(code,  LEN(code) - CHARINDEX('.', REVERSE(code), 2)))) -1
            )
        )   second_last_value
FROM #temp

second_last_value
-----------------------------
104150
204150