表Tmp数据
id value
1 0.9%氯化钠注射液,0.9%:500ml,山东华鲁制药有限公司,20,瓶,1.380,
2 0.9%氯化钠注射液,100ml*80,上海华源长富药业(集团)有限公司,80,塑瓶,0.600,
3 0.9%氯化钠注射液,500ml*30,上海华源长富药业(集团)有限公司,30,塑瓶,2.850,
4 0.9%氯化钠注射液,500ml,六安华源制药有限公司,20,瓶,1.430,
5 0.9氯化钠注射液,250ml*30,山东华鲁制药有限公司,30,瓶,0.980,
6 0.9氯化钠注射液,500ml,山东华鲁制药有限公司,20,瓶,0.800,根据指定符号,分割字符后并插入到新表中,求一SQL语句
新表如下:
id(int) p1(varchar50) p2(varchar50) p3(varchar50) p4(varchar50) p5(varchar50) p6(varchar50)想要的结果如：
id(int) p1(varchar50) p2(varchar50) p3(varchar50) p4(varchar50) p5(varchar50) p6(varchar50)
1 0.9%氯化钠注射液 0.9%:500ml 山东华鲁制药有限公司 20 瓶 1.380
id value
1 0.9%氯化钠注射液,0.9%:500ml,山东华鲁制药有限公司,20,瓶,1.380,
2 0.9%氯化钠注射液,100ml*80,上海华源长富药业(集团)有限公司,80,塑瓶,0.600,
3 0.9%氯化钠注射液,500ml*30,上海华源长富药业(集团)有限公司,30,塑瓶,2.850,
4 0.9%氯化钠注射液,500ml,六安华源制药有限公司,20,瓶,1.430,
5 0.9氯化钠注射液,250ml*30,山东华鲁制药有限公司,30,瓶,0.980,
6 0.9氯化钠注射液,500ml,山东华鲁制药有限公司,20,瓶,0.800,根据指定符号,分割字符后并插入到新表中,求一SQL语句
新表如下:
id(int) p1(varchar50) p2(varchar50) p3(varchar50) p4(varchar50) p5(varchar50) p6(varchar50)想要的结果如：
id(int) p1(varchar50) p2(varchar50) p3(varchar50) p4(varchar50) p5(varchar50) p6(varchar50)
1 0.9%氯化钠注射液 0.9%:500ml 山东华鲁制药有限公司 20 瓶 1.380
(
@String NVARCHAR(1210), -- 要分隔的字符串
@SPLITCHAR NVARCHAR(10) = ',', -- 默认分隔字符
@TableName NVARCHAR(30) = 'table' --默认表名
)
AS
DECLARE @L INT -- 第一个分隔字符的位置
DECLARE @S INT -- 第二个分隔字符的位置
SET @L = 0
SET @S = CHARINDEX(@SPLITCHAR, @String, @L)WHILE @L <= LEN(@String)
BEGIN
DECLARE @ColName NVARCHAR(50)
IF @S = 0 SET @S = LEN(@String) + 1 -- 如果到最后一个字符串那么第二个分隔字符的位置就是这个字符串的长度加一
SET @ColName = SUBSTRING(@String, @L, @S - @L) -- 取值
SET @L = @S + 1
SET @S = CHARINDEX(@SPLITCHAR, @String, @L)
IF LTRIM(RTRIM(@ColName)) = '' CONTINUE -- 如果是空字符串就跳过
DECLARE @SQL NVARCHAR(1000)
SET @SQL ='INSERT INTO ' + @TableName + '(tcname) select ''' +@ColName+'''' --插入表的语句，根据自己需求修改
--EXEC (@SQL)
Print @sql
END
a.id,b.[value]
from
(select id,[value]=convert(xml,'<root><v>'+replace([value],',','</v><v>')+'</v></root>') from Tb)a
outer apply
(select [value]=C.v.value('.','nvarchar(100)') from a.[value].nodes('/root/v')C(v))b
-->Author:happy_stone【不會飛的石頭】
-->Date :2009-10-19 15:48:36
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[value] nvarchar(49))
Insert tb
select 1,N'0.9%氯化钠注射液,0.9%:500ml,山东华鲁制药有限公司,20,瓶,1.380,' union all
select 2,N'0.9%氯化钠注射液,100ml*80,上海华源长富药业(集团)有限公司,80,塑瓶,0.600,' union all
select 3,N'0.9%氯化钠注射液,500ml*30,上海华源长富药业(集团)有限公司,30,塑瓶,2.850,' union all
select 4,N'0.9%氯化钠注射液,500ml,六安华源制药有限公司,20,瓶,1.430,' union all
select 5,N'0.9氯化钠注射液,250ml*30,山东华鲁制药有限公司,30,瓶,0.980,' union all
select 6,N'0.9氯化钠注射液,500ml,山东华鲁制药有限公司,20,瓶,0.800,'
Go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s nvarchar(4000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split nvarchar(10) --数据分隔符
)RETURNS nvarchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
select ID,
dbo.f_GetStr([value],1,',')[P1],
dbo.f_GetStr([value],2,',')[P2],
dbo.f_GetStr([value],3,',')[P3],
dbo.f_GetStr([value],4,',')[P4],
dbo.f_GetStr([value],5,',')[P5],
dbo.f_GetStr([value],6,',')[P6]
from tb
/*(6 個資料列受到影響)
ID P1 P2 P3 P4 P5 P6
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 0.9%氯化钠注射液 0.9%:500ml 山东华鲁制药有限公司 20 瓶 1.380
2 0.9%氯化钠注射液 100ml*80 上海华源长富药业(集团)有限公司 80 塑瓶 0.600
3 0.9%氯化钠注射液 500ml*30 上海华源长富药业(集团)有限公司 30 塑瓶 2.850
4 0.9%氯化钠注射液 500ml 六安华源制药有限公司 20 瓶 1.430
5 0.9氯化钠注射液 250ml*30 山东华鲁制药有限公司 30 瓶 0.980
6 0.9氯化钠注射液 500ml 山东华鲁制药有限公司 20 瓶 0.800(6 個資料列受到影響)
*/
搞定，散分