問題描述

我開始使用 T-SQL 分析日志.我有一個(gè)問題無法解決.

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"

所以當(dāng)我知道我有多少價(jià)值時(shí)，我沒有問題，例如:

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

我使用代碼部分的代碼來分割這些數(shù)據(jù)，如下所示:

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

但是當(dāng)我不知道日志字符串中有多少值"條目時(shí)，我不知道如何處理如下日志:

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

推薦答案

使用拆分函數(shù)(在 SQL Server 2016 之前有很多方法可以做到這一點(diǎn)，這里是一種):

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 等和未知端點(diǎn)更簡單:

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;

這篇關(guān)于SQL Server 2014 中的函數(shù) STRING_SPLIT的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！