我有一个字符串,需要在从中提取值后插入到表中。示例字符串如下所示:
111111,1111,11.11,'2019-01-01-11.11.11.111111'#222222,2222,22.22,'2019-02-02-22.22.22.222222'使用string_split,我可以将值拆分到不同的行中:
declare @s varchar(1000)
set @s = '111111,1111,11.11,''2019-01-01-11.11.11.111111''#222222,2222,22.22,''2019-02-02-22.22.22.222222'''
select value from string_split(@s, '#')
--Output
--111111,1111,11.11,'2019-01-01-11.11.11.111111'
--222222,2222,22.22,'2019-02-02-22.22.22.222222'变得有趣了。
需要将输出插入到表中。我考虑过动态SQL,但不太喜欢这样:
declare @SQLstatment varchar(1000)
set @SQLstatment = 'insert into MyTable (Col1,Col2,Col3,Col4) values (' + OUTPUT_FROM_SPLIT + ')'
exec (@SQLstatment)变得更有趣了。
Col4是DB2数据类型的timestamp。我需要使用以下语句将其转换为Server 2016 datetime2数据类型:
declare @DB2_timpstamp varchar(30) = '2019-02-25-11.22.33.456789'
select convert(datetime2, stuff(stuff(stuff(@DB2_timpstamp, 17, 1, ':'), 14, 1, ':'), 11, 1, ' '))问题
我不能让这一切都起作用。
我希望尽可能避免使用动态SQL。很可能我得用循环。解决方案将如下所示:
while(SOME_CONDITION)
begin
insert into MyTable (Col1,Col2,Col3,Col4) values
(Val1,Val2,Val3,convert(datetime2, stuff(stuff(stuff(Val4, 17, 1, ':'), 14, 1, ':'), 11, 1, ' ')))
LOOP_VARIABLE_INCREMENT
end发布于 2019-03-08 15:57:07
好的,不是SQL Server转换函数方面的专家,但这将使您走上正确的轨道:
with
r (value) as ( -- raw varchar value
select value from string_split(@s, '#')
),
x (col1, col2, col3, col4) as ( -- values in separate columns
select
substring(value, 1, 10) as col1, -- fix conversion here
substring(value, 11, 10) as col2, -- fix conversion here
substring(value, 21, 10) as col3, -- fix conversion here
substring(value, 31, 10) as col4, -- fix conversion here
from r
)
insert into MyTable (Col1,Col2,Col3,Col4) -- insert now
select col1, col2, col3, col4 from x正如你所看到的,没有必要进行循环。只要普通的SQL就行了。当然,您需要稍微调整一下转换公式,但这将使您非常接近。
发布于 2019-03-08 15:55:15
你需要分开两次:
declare @s varchar(1000)
set @s = '111111,1111,11.11,''2019-01-01-11.11.11.111111''#222222,2222,22.22,''2019-02-02-22.22.22.222222'''
;WITH cte AS (
select value
from string_split(@s, '#')
)
SELECT
MAX(CASE WHEN ord=1 THEN v END) AS col1,
MAX(CASE WHEN ord=2 THEN v END) AS col2,
MAX(CASE WHEN ord=3 THEN v END) AS col3,
MAX(CASE WHEN ord=4 THEN v END) AS col4
FROM cte
CROSS APPLY (SELECT value AS v, ROW_NUMBER() OVER(ORDER BY 1/0) AS ord
FROM STRING_SPLIT([value], ',')) s
GROUP BY value;发布于 2019-03-08 15:54:19
使用DelimitedSplit8k_Lead的强大功能,您可以将项目拆分两次,并知道它们的序号位置(对于旋转非常重要)。给你一个很好的简单陈述:
DECLARE @DataSet varchar(MAX) = '111111,1111,11.11,''2019-01-01-11.11.11.111111''#222222,2222,22.22,''2019-02-02-22.22.22.222222''';
--INSERT INTO MyTable (Col1,Col2,Col3,Col4)
SELECT MAX(CASE WHEN C.ItemNumber = 1 THEN C.item END),
MAX(CASE WHEN C.ItemNumber = 2 THEN C.item END),
MAX(CASE WHEN C.ItemNumber = 3 THEN C.item END),
MAX(CASE WHEN C.ItemNumber = 4 THEN TRY_CONVERT(Datetime2(6),STUFF(STUFF(STUFF(C.item,17,1,':'),14,1,':'),11,1,'T')) END)
FROM dbo.DelimitedSplit8K_lead(@DataSet,'#') R
CROSS APPLY dbo.DelimitedSplit8K_lead (REPLACE(R.item,'''',''),',') C --Because we don't want those nasty single quotes!
GROUP BY R.item;https://stackoverflow.com/questions/55066523
复制相似问题