求SQL 2005 一个查询语句

ijwsoft 2015-11-15 03:45:21
有一个字符串: 大泽山1号1份4.8精品烟台红富士751份5.6精选海南香蕉(2斤装)1份3.2
如何得到以下的查询结果:
大泽山1号 1份 4.8
精品烟台红富士 751份 5.6
精选海南香蕉(2斤装) 1份 3.2

运行环境 SQL 2005,谢谢了。
...全文
451 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
zbdzjx 2017-04-01
  • 打赏
  • 举报
回复
同意6楼说的,在前端保存时,先处理一下。 可以用一些特殊的符号进行分隔,这样在拆分时,就容易的多了。
道素 2017-04-01
  • 打赏
  • 举报
回复
SQL Server 2005比较麻烦 这里分隔项目依据PATINDEX(N'[0-9][^号份斤.0-9]%',s) 来查找项目中的分隔

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
javon_liu 2015-11-25
  • 打赏
  • 举报
回复
引用 7 楼 xxzxwsx 的回复:

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
---------------------*/
学习
BOKYYY 2015-11-24
  • 打赏
  • 举报
回复
都是以份为单位的话,可以截取字符串
在路上_- 2015-11-24
  • 打赏
  • 举报
回复

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
---------------------*/
Yole 2015-11-16
  • 打赏
  • 举报
回复

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 行受影响)
*/
苦苦的潜行者 2015-11-16
  • 打赏
  • 举报
回复
奇葩啊....建议往数据库里存的时候就分开或者用程序的时候先用正则处理一下.不然sql可不支持正则啊.
comma212164 2015-11-16
  • 打赏
  • 举报
回复
貌似没有什么规律啊,
在路上_- 2015-11-16
  • 打赏
  • 举报
回复
预防商品名称中出现“份”字

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
------------------------*/

在路上_- 2015-11-16
  • 打赏
  • 举报
回复

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
------------------------*/
ijwsoft 2015-11-15
  • 打赏
  • 举报
回复
顶一下。麻烦了。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧