22,206
社区成员
发帖
与我相关
我的任务
分享
DECLARE @s NVARCHAR(max)=N'大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2'
SELECT LEFT(@s,PATINDEX(N'%[0-9][^号份斤.]%',@s))
CREATE TABLE #s(s NVARCHAR(500))
INSERT INTO #s(s)valueS(N'大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2')
CREATE TABLE #pos(ID INT IDENTITY(1,1),s NVARCHAR(500),pos int)
INSERT INTO #pos(s, pos)
SELECT t.s,sv.number
FROM master.dbo.spt_values AS sv INNER JOIN #s AS t ON sv.number BETWEEN 1 AND LEN(t.s) AND (PATINDEX(N'[0-9][^号份斤.0-9]%',SUBSTRING(t.s,sv.number,LEN(t.s)))>0 OR sv.number=LEN(t.s))
WHERE sv.type='P'
SELECT SUBSTRING(p1.s,ISNULL(pp1.pos,0)+1,p1.pos-ISNULL(pp1.pos,0))
FROM #pos AS p1
LEFT JOIN #pos AS pp1 ON pp1.ID=p1.ID-1
如果是SQL Server 2012+就省事多了
;WITH tb(s)AS(
SELECT N'大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2'
)
SELECT t.s,sv.number
,LAG(sv.number)OVER(ORDER BY sv.number) AS n1
,SUBSTRING(t.s,ISNULL(LAG(sv.number)OVER(ORDER BY sv.number),0)+1,sv.number-ISNULL(LAG(sv.number)OVER(ORDER BY sv.number),0))
FROM master.dbo.spt_values AS sv INNER JOIN tb AS t ON sv.number BETWEEN 1 AND LEN(t.s)
CROSS APPLY(VALUES(PATINDEX(N'[0-9][^号份斤.0-9]%',SUBSTRING(t.s,sv.number,LEN(t.s)))))c(b)
WHERE sv.type='P' AND (c.b>0 OR sv.number=LEN(t.s))
s number n1 (No column name)
大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2 10 NULL 大泽山1号1份4.8
大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2 24 10 精品烟台红富士751份5.6
大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2 40 24 精选海南香蕉(2斤装)1份3.2
CREATE FUNCTION dbo.fn_splt(@str nvarchar(500))
RETURNS @RE TABLE(商品名称 nvarchar(30), 数量 nvarchar(10), 金额 decimal(9,2))
AS
BEGIN
--declare @str nvarchar(500)='大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2';
DECLARE @s nvarchar(500);
SET @s= @str;
DECLARE @i smallint, @c1 smallint, @c2 smallint, @c3 smallint;
WHILE LEN(@s)>0
BEGIN
SET @i= PATINDEX('%[0-9]份[0-9]%', @s);
SET @c1= PATINDEX('%[^0-9,.]%', REVERSE(LEFT(@s, @i-1)));
SET @c1= @i-@c1; --第1列长度
SET @c2= @i+1-@c1; --第2列长度
SET @c3= PATINDEX('%[^0-9,.]%', SUBSTRING(@s, @i+3, 500));
IF @c3=0 SET @c3=LEN(@s)-@c1-@c2; --第3列长度
INSERT INTO @RE
SELECT LEFT(@s, @c1) 商品名称
, SUBSTRING(@s, @c1+1, @c2) 数量
, SUBSTRING(@s, @c1+@c2+1, @c3) 金额;
SET @s=RIGHT(@s, LEN(@s)-@c1-@c2-@c3);
END;
RETURN;
END
GO
SELECT * from fn_splt('大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2');
/*--------结果-------
商品名称 数量 金额
---------------------
大泽山1号 1份 4.80
精品烟台红富士 751份 5.60
精选海南香蕉(2斤装) 1份 3.20
---------------------*/
select substring('大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2',1,10)
union
select substring('大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2',11,14)
union
select substring('大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2',25,16)
/*
--------------------------------
大泽山1号1份4.8
精品烟台红富士751份5.6
精选海南香蕉(2斤装)1份3.2
(3 行受影响)
*/
ALTER FUNCTION dbo.fn_splt(@str nvarchar(500))
RETURNS @RE TABLE(商品明细 nvarchar(200))
AS
BEGIN
--declare @str nvarchar(500)='大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2';
DECLARE @s nvarchar(500);
SET @s= @str;
DECLARE @i smallint, @j smallint;
WHILE LEN(@s)>0
BEGIN
SET @i= PATINDEX('%[0-9]份[0-9]%', @s);
SET @j= PATINDEX('%[^0-9,.]%', SUBSTRING(@s, @i+3, 500));
IF @j=0 SET @i=LEN(@s) ELSE SET @i= @i + @j + 1;
INSERT INTO @RE
SELECT LEFT(@s, @i) 商品明细;
SET @s=RIGHT(@s, LEN(@s)-@i);
END;
RETURN;
END
SELECT * from fn_splt('大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2');
/*-------结果----------
商品明细
----------------
大泽山1号1份4.8
精品烟台红富士751份5.6
精选海南香蕉(2斤装)1份3.2
------------------------*/
ALTER FUNCTION dbo.fn_splt(@str nvarchar(500))
RETURNS @RE TABLE(商品明细 nvarchar(200))
AS
BEGIN
--declare @str nvarchar(500)='大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2';
DECLARE @s nvarchar(500);
SET @s= @str;
DECLARE @i smallint, @j smallint;
WHILE LEN(@s)>0
BEGIN
SET @i= CHARINDEX('份', @s);
SET @j= PATINDEX('%[^0-9,.]%', SUBSTRING(@s, @i+1, 500));
IF @j=0 SET @i=LEN(@s) ELSE SET @i= @i + @j - 1;
INSERT INTO @RE
SELECT LEFT(@s, @i) 商品明细;
SET @s=RIGHT(@s, LEN(@s)-@i);
END;
RETURN;
END
SELECT * from fn_splt('大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2');
/*-------结果----------
商品明细
----------------
大泽山1号1份4.8
精品烟台红富士751份5.6
精选海南香蕉(2斤装)1份3.2
------------------------*/