varchar 转 float及行列转换问题

[tengdacom]

有订单表如下
名称（VARCHAR（40） 需求数量，备注（VARCHAR（400）
A电线 100 红色25捆，兰色25捆，绿色30捆，黄色20捆

希望得到的结果是如下，数量字段要求是FLOAT型
A电线 红 25
A电线 兰 25
A电线 绿 30
A电线 黄 20

[SQL77]

拆分函数,

[SQL77]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
	DECLARE @splitlen int
	SET @splitlen=LEN(@split+'a')-2
	WHILE CHARINDEX(@split,@s)>0
	BEGIN
		INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
		SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
	END
	INSERT @re VALUES(@s)
	RETURN
END
GO


/*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.3.1 使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
	--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
	DECLARE @t TABLE(ID int IDENTITY,b bit)
	INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

	INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
	FROM @t
	WHERE ID<=LEN(@s+'a') 
		AND CHARINDEX(@split,@split+@s,ID)=ID
	RETURN
END
GO

/*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
GO

--3.2.3.2 使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO

--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s     varchar(8000),  --待分拆的字符串
@split  varchar(10)     --数据分隔符
)RETURNS TABLE
AS
RETURN(
	SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
	FROM tb_splitSTR
	WHERE ID<=LEN(@s+'a') 
		AND CHARINDEX(@split,@split+@s,ID)=ID)
GO


/*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.5 将数据项按数字与非数字再次拆份
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),    --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(No varchar(100),Value varchar(20))
AS
BEGIN
	--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
	DECLARE @t TABLE(ID int IDENTITY,b bit)
	INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

	INSERT @re 
	SELECT	No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
		Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
	FROM(
		SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
		FROM @t
		WHERE ID<=LEN(@s+'a') 
			AND CHARINDEX(@split,@split+@s,ID)=ID)a
	RETURN
END
GO

[SQL77]

动态拆分
declare @sql varchar(8000)
set @sql='a:a:3052169:76:102:张三:100:0:0:0'
set @sql='select '''+replace(@sql,':',''' union all select ''')+''''
exec(@sql)

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------------
   --------------------希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( 名称 varchar(10),需求数量 int,备注 varchar(100) )
go
insert tb SELECT 
'A电线',   100 ,     '红色25捆,兰色25捆,绿色30捆,黄色20捆' 
go
select 名称,
颜色=LEFT(东西,1),
数量=cast(reverse(stuff(reverse(STUFF(东西,1,2,'')),1,1,'')) as float)
from(
Select
    a.名称,东西=substring(a.备注,b.number,charindex(',',a.备注+',',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.备注)
where
     substring(','+a.备注,b.number,1)=',') t
go
/*
名称         颜色   数量
---------- ---- ----------------------
A电线        红    25
A电线        兰    25
A电线        绿    30
A电线        黄    20
*/

[guguda2008]

不好写，闪

[guguda2008]

[Quote=引用 4 楼 feixianxxx 的回复:]
SQL code-- =========================================
-- -----------t_mac 小编---------------------------------------希望有天成为大虾----
-- =========================================IFOBJECT_ID('tb')ISNOTNULLDROPTABLE tbGOCREATETABLE tb( 名称varchar(10),需求数量int,备注varchar(100) )goinsert tbSELECT'A电线',100 ,'红色25捆,兰色25捆,绿色30捆,黄色20捆'goselect 名称,
颜色=LEFT(东西,1),
数量=cast(reverse(stuff(reverse(STUFF(东西,1,2,'')),1,1,''))asfloat)from(Select
a.名称,东西=substring(a.备注,b.number,charindex(',',a.备注+',',b.number)-b.number)from
Tb ajoin master..spt_values bON B.type='p'AND B.numberBETWEEN1ANDLEN(A.备注)wheresubstring(','+a.备注,b.number,1)=',') tgo/*
名称 颜色 数量
---------- ---- ----------------------
A电线 红 25
A电线 兰 25
A电线 绿 30
A电线 黄 20*/
[/Quote]
学习

[tengdacom]

名称（VARCHAR（40） 需求数量，备注（VARCHAR（400）
A电线 100 红色25捆，兰色25捆，绿色30捆，黄色20捆。库存取

备注字段，花色写完后，有可能有库存取三个字，也有可能没有？请问要怎么办？

[feixianxxx]

[Quote=引用 7 楼 tengdacom 的回复:]
名称（VARCHAR（40） 需求数量，备注（VARCHAR（400）
A电线                100      红色25捆，兰色25捆，绿色30捆，黄色20捆。库存取

备注字段，花色写完后，有可能有库存取三个字，也有可能没有？请问要怎么办？

[/Quote]
有的话 这三个字你想放哪

[tengdacom]

另外，我希望写成函数，因为出来的数据还要和发货表相减后，显示在库存表里。所以我还要调用

[tengdacom]

这三个字放在最后

[tengdacom]

如果备注是写成红黄蓝绿各25,又怎么分?