SQL表变量截取字符串内容与字符串内容根据符号进行拆分

方法一  表变量

DECLARE @#sou TABLE (s NVARCHAR(100),y NVARCHAR(100),x DATETIME)
INSERT INTO @#sou 
----Name，Temp为字符串内容标签名---
SELECT 
(SELECT Value FROM OPENJSON((SELECT Value FROM  OPENJSON(TempList) WHERE [Key]=0)) WHERE [Key]='Name') AS s,
(SELECT Value FROM OPENJSON((SELECT Value FROM  OPENJSON(TempList) WHERE [Key]=0)) WHERE [Key]='Temp') AS y,
CreateTime AS x FROM 表名
WHERE IP='IP'AND TempList<>''
UNION all
SELECT 
(SELECT Value FROM OPENJSON((SELECT Value FROM  OPENJSON(TempList) WHERE [Key]=1)) WHERE [Key]='Name') AS s,
(SELECT Value FROM OPENJSON((SELECT Value FROM  OPENJSON(TempList) WHERE [Key]=1)) WHERE [Key]='Temp') AS y,
CreateTime AS x FROM  表名 
WHERE IP='ip'AND TempList<>''
UNION all
SELECT 
(SELECT Value FROM OPENJSON((SELECT Value FROM  OPENJSON(TempList) WHERE [Key]=2)) WHERE [Key]='Name') AS s,
(SELECT Value FROM OPENJSON((SELECT Value FROM  OPENJSON(TempList) WHERE [Key]=2)) WHERE [Key]='Temp') AS y,
CreateTime AS x FROM  表名
WHERE IP='ip'AND TempList<>''
UNION all
SELECT 
(SELECT Value FROM OPENJSON((SELECT Value FROM  OPENJSON(TempList) WHERE [Key]=3)) WHERE [Key]='Name') AS s,
(SELECT Value FROM OPENJSON((SELECT Value FROM  OPENJSON(TempList) WHERE [Key]=4)) WHERE [Key]='Temp') AS y,
CreateTime AS x FROM  表名
WHERE IP='ip'AND TempList<>''

SELECT * FROM @#sou

方法二

1、定义方法

CREATE FUNCTION f_splitstr(@SoureeSql NVARCHAR(MAX),@StrSeprate NVARCHAR(100))
RETURNS @temp TABLE(F1 NVARCHAR(100))
AS 
BEGIN
DECLARE @ch AS NVARCHAR(100)

SET @SoureeSql=@SoureeSql+@StrSeprate
  WHILE(@SoureeSql<>'')
  BEGIN
  SET @ch=LEFT(@SoureeSql,CHARINDEX(',',@SoureeSql,1)-1)
  INSERT @temp VALUES(@ch)
  SET @SoureeSql=STUFF(@SoureeSql,1,CHARINDEX(',',@SoureeSql,1),'')
  END
  RETURN
END

2、调用方法

DECLARE @TempList AS NVARCHAR(MAX)

SET @TempList=(    SELECT TOP 1 
    --ToolInfoS--刀具
    --MainAxisList--主轴
    --ServiceList   --伺服
    TempList--温度

    FROM dbo.MKDHistoryStatus
    WHERE   IP='10.0.142.107' 
    AND  CreateTime BETWEEN '2023-01-01' AND '2023-01-05'
    ORDER BY CreateTime ASC
)

SELECT * FROM dbo.f_splitstr(@TempList,',')