SQL Server 2014 中的函数 STRING_SPLIT

2021-09-10 00:00:00 sql tsql sql-server

我开始使用 T-SQL 分析日志.我有一个问题无法解决.

I started analyze logs with T-SQL. I have one problem i can't deal with it.

示例消息:

LogsID = "1" Value="1" Value="2" Value="3"

所以当我知道我有多少价值时，我没有问题，例如:

So I don't have problem with split this when I know how much I have Value, for example :

我使用代码部分的代码来分割这些数据，如下所示:

I use code from code section to split this data like below : 

LogsID  Value
---------------    
   1      1

代码:

DECLARE @String VARCHAR(1024);

SET @String = 'LogsID = "1" Value="1" Value="2" Value="3"'

SELECT 
    SUBSTRING(@String, 
              CHARINDEX('Logs_ID="', @String) + 11, 
              CHARINDEX('Value="', @String) - (CHARINDEX('Logs_ID="', @String) + 13))

但是当我不知道日志字符串中有多少值"条目时，我不知道如何处理如下日志:

But I don't know how deal with logs like below when I don't know how many "value" entries are in the log string:

LogsID = "1" Value="1" Value="2" Value="3"

我想像这样拆分这些信息:

I want split this information like that : 

LogsID  Value
------------    
   1    1
   1    2
   1    3

推荐答案

使用拆分函数(在 SQL Server 2016 之前有很多方法可以做到这一点，这里是一种):

With a split function (there are many ways to do this prior to SQL Server 2016, here is one):

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value] FROM 
      ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
      ) AS y
    );

查询变得比一堆 charindex 等和未知端点更简单:

The query becomes simpler than a bunch of charindex etc. and an unknown endpoint:

DECLARE @String VARCHAR(1024) = 'LogsID = "1" Value="1" Value="2" Value="3"';

;WITH x AS
(
  SELECT value, p = PARSENAME(value,1) 
  FROM dbo.SplitString(@String, 'Value=')
),
l AS
(
  SELECT LogsID = PARSENAME(LTRIM(SUBSTRING(value,CHARINDEX('=',value)+1,255)),1) 
  FROM x WHERE p IS NULL
)
SELECT l.LogsID,y.p FROM l
CROSS JOIN (SELECT p FROM x WHERE p IS NOT NULL) AS y;

相关文章