如果使用SQL Server 2016+,则可以使用STRING_SPLIT()
尝试以下方法:
表格:
CREATE TABLE Data (
TextData nvarchar(max)
)
INSERT INTO Data
(TextData)
VALUES
(N'2238455|12/10/2019 03:07:55 PM;2238452|12/10/2019 03:13:39 PM;2238454|12/10/2019 03:25:04 PM;2238453|12/10/2019 03:26:44 PM')
声明:
SELECT
LEFT(s.[value],CHARINDEX(N'|',s.[value]) - 1) AS Barcode,RIGHT(s.[value],LEN(s.[value]) - CHARINDEX(N'|',s.[value])) AS [DateTime]
FROM Data d
CROSS APPLY STRING_SPLIT(d.TextData,N';') s
结果:
Barcode DateTime
2238455 12/10/2019 03:07:55 PM
2238452 12/10/2019 03:13:39 PM
2238454 12/10/2019 03:25:04 PM
2238453 12/10/2019 03:26:44 PM
,
对于较早的版本,您可以使用此函数将字符串转换为表
create Function [dbo].[fn_StringToTable]
(
@List Varchar(max)
)
RETURNS @Table TABLE (barcode VARCHAR(100),newdate VARCHAR(100))
AS
BEGIN
IF RIGHT(@List,1) <> ';'
SELECT @List = @List + ';'
DECLARE @Pos BIGINT,@OldPos BIGINT,@rowval varchar(100)
SELECT @Pos = 1,@OldPos = 1
WHILE @Pos < LEN(@List)
BEGIN
SELECT @Pos = CHARINDEX(';',@List,@OldPos)
select @rowval = LTRIM(RTRIM(SUBSTRING(@List,@OldPos,@Pos - @OldPos)))
INSERT INTO @Table(barcode,newdate)
SELECT SUBSTRING(@rowval,1,CHARINDEX('|',@rowval)-1),SUBSTRING(@rowval,@rowval)+1,LEN(@rowval))
SELECT @OldPos = @Pos + 1
END
RETURN
END
然后使用字符串调用此函数
select * from [dbo].[fn_StringToTable] ('2238455|12/10/2019 03:07:55 PM;2238452|12/10/2019 03:13:39 PM;2238454|12/10/2019 03:25:04 PM;2238453|12/10/2019 03:26:44 PM')
OutPut
barcode newdate
2238455 12/10/2019 03:07:55 PM
2238452 12/10/2019 03:13:39 PM
2238454 12/10/2019 03:25:04 PM
2238453 12/10/2019 03:26:44 PM
本文链接:https://www.f2er.com/2941683.html