27,580
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(54))
Insert #T
select N'2050640*10+2050641*500' union all
select N'2050640*10+2050641*500+2050642*250'
Go
--测试数据结束
SELECT t.*
FROM #T
CROSS APPLY ( SELECT SUBSTRING(f1, 1, CHARINDEX('*', f1) - 1) AS id ,
SUBSTRING(f1, CHARINDEX('*', f1) + 1,
LEN(f1) - CHARINDEX('*', f1)) AS shl
FROM dbo.f_splitstr(col, '+')
) t
DECLARE @str VARCHAR(1000)='2050640*10+2050641*500+2050642*250'
SELECT T3.*
FROM (VALUES(CONVERT(XML,'<r><c>'+REPLACE('<col>'+REPLACE(@str,'*','</col><col>')+'</col>','+','</col></c><c><col>')+'</c></r>')))T(C)
CROSS APPLY(SELECT T2.C.value('col[1]','int'),T2.C.value('col[2]','int') FROM T.C.nodes('r/c') AS T2(C)) AS T3(Col1,Col2)
/*
Col1 Col2
2050640 10
2050641 500
2050642 250
*/
CREATE FUNCTION dbo.f_splitstr(@SourceSql NVARCHAR(MAX),@StrSeprate VARCHAR(100))
RETURNS @temp TABLE(F1 VARCHAR(100))
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
SET @SourceSql=@SourceSql+@StrSeprate
WHILE(@SourceSql<>'')
BEGIN
SET @ch=LEFT(@SourceSql,CHARINDEX(@StrSeprate,@SourceSql,1)-1)
INSERT @temp VALUES(@ch)
SET @SourceSql=STUFF(@SourceSql,1,CHARINDEX(@StrSeprate,@SourceSql,1),'')
END
RETURN
END
GO
DECLARE @str NVARCHAR(MAX)= '2050640*10+2050641*500+2050642*250'
SELECT SUBSTRING(f1, 1, CHARINDEX('*', f1) - 1) AS id ,
SUBSTRING(f1, CHARINDEX('*', f1) + 1, LEN(f1) - CHARINDEX('*', f1)) AS shl
FROM dbo.f_splitstr(@str, '+')
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(54))
Insert #T
select N'2050640*10+2050641*500' union all
select N'2050640*9+2050641*50+2050642*250'
Go
--测试数据结束
SELECT t.*
FROM #t
CROSS APPLY ( SELECT SUBSTRING(f1, 1, CHARINDEX('*', f1) - 1) AS id ,
SUBSTRING(f1, CHARINDEX('*', f1) + 1,
LEN(f1) - CHARINDEX('*', f1)) AS shl
FROM dbo.f_splitstr(col, '+')
) t