34,576
社区成员
发帖
与我相关
我的任务
分享
'87349+2015-06-10 10:38_2015-06-23 10:38+20.00
$109603+2015-06-16 10:38_2015-06-22 10:38+0.00'
;WITH act AS (
SELECT dbo.fn_GetSplitStr(fs.cValue, '+', 1) stuID,
dbo.fn_GetSplitStr(fs.cValue, '+', 2) dateList,
dbo.fn_GetSplitStr(fs.cValue, '+', 3) FeeStand
FROM dbo.fn_StrSplitToTable(@SelInfo,' $') AS fs )
SELECT * FROM act
\$?(\d+)\+([^_]+)_([^\+]+)\+([^$]+)
/*
87349+2015-06-10 10:38_2015-06-23 10:38+20.00$109603+2015-06-16 10:38_2015-06-22 10:38+0.00
Group 1 Group 2 Group 3 Group 4
87349 2015-6-10 10:38 2015-6-23 10:38 20.00
109603 2015-6-16 10:38 2015-6-22 10:38 0.00
*/
DECLARE @STRIN VARCHAR(MAX)
SET @STRIN='87349+2015-06-10 10:38_2015-06-23 10:38+20.00
$109603+2015-06-16 10:38_2015-06-22 10:38+0.00'
;WITH CTE AS(
SELECT RTRIM(T2.V)V,CHARINDEX('+',T2.V)N1
,CHARINDEX('_',T2.V)N2
,CHARINDEX('+',T2.V,CHARINDEX('+',T2.V)+1)N3
FROM(SELECT CAST('<V>'+REPLACE(@STRIN,'$','</V><V>')+'</V>'AS XML)VS)T1
CROSS APPLY(SELECT N.V.value('.','VARCHAR(100)')V FROM T1.VS.nodes('/V')N(V))T2
)
,CTE2 AS(
SELECT LEFT(V,N1-1)AS[stuID]
,SUBSTRING(V,N1+1,N2-N1-1)[date1]
,SUBSTRING(V,N2+1,N3-N2-1)[date2]
,RIGHT(V,LEN(V)-N3)[FeeStand]
FROM CTE
)
SELECT [stuID],[date],[FeeStand]
FROM CTE2 UNPIVOT([date] FOR T IN([date1],[date2]))U
Create function [dbo].[split]
(
@SourceSql varchar(max),
@StrSeprate varchar(10)
)
returns @temp table(line varchar(max))
as
begin
declare @i int
set @SourceSql = rtrim(ltrim(@SourceSql))
set @i = charindex(@StrSeprate,@SourceSql)
while @i >= 1
begin
if len(left(@SourceSql,@i-1))>0
begin
insert @temp values(left(@SourceSql,@i-1))
end
set @SourceSql=substring(@SourceSql,@i+len(@StrSeprate),len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql <> ''
insert @temp values(@SourceSql)
return
end