sql 如何将多个{$}字符串拆分成多条数据

lilo202 2016-01-08 11:06:11

把数据：
布展完毕 picurl{$}pic2

拆分成
布展完毕测试 pic
布展完毕测试 pic

1、创建表



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[pic_list](

	[id] [int] IDENTITY(1,1) NOT NULL,

	[title] [nvarchar](max) NULL,

	[summary] [nvarchar](max) NULL,

	[picurlNew] [nvarchar](max) NULL,

	[createtime] [datetime] NULL,

	[sort] [int] NULL,

 CONSTRAINT [PK_ht_newssss] PRIMARY KEY CLUSTERED 

(

	[id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[pic_list] ON

INSERT [dbo].[pic_list] ([id], [title], [summary], [picurlNew], [createtime], [sort]) VALUES (1, N'挑灯夜战', N'图片介绍1', N'{$}http://www.xxx.cn/upload/2016/1/2/2016_1_2_20_53_7_965_size_1024_768.jpg{$}', CAST(0x0000A58101647F48 AS DateTime), NULL)

INSERT [dbo].[pic_list] ([id], [title], [summary], [picurlNew], [createtime], [sort]) VALUES (2, N'布展完毕', N'图片介绍2', N'{$}http://www.xxx.cn/upload/2016/1/3/2016_1_3_0_57_9_93_size_1334_1234_.jpg{$}http://www.xxx.cn/upload/2016/1/3/2016_1_3_0_57_10_791.jpg{$}', CAST(0x0000A58101647F48 AS DateTime), NULL)

INSERT [dbo].[pic_list] ([id], [title], [summary], [picurlNew], [createtime], [sort]) VALUES (3, N'模特', N'图片介绍3', N'{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_9_52_856_size_1242_1641_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_9_53_886_size_1242_1641_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_9_54_536_size_678_678_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_9_54_991_size_960_960_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_9_56_271_size_1656_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_9_57_616_size_1656_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_9_59_461_size_1656_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_10_1_246_size_1656_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_10_3_6_size_1656_2208_.jpg{$}', CAST(0x0000A58101647F48 AS DateTime), NULL)

INSERT [dbo].[pic_list] ([id], [title], [summary], [picurlNew], [createtime], [sort]) VALUES (4, N'看那里', N'图片介绍4', N'{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_22_58_756_size_1242_1641_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_23_0_356_size_1656_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_23_1_336_size_1064_959_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_23_3_256_size_1656_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_23_6_370_size_1656_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_23_8_291_size_1242_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_23_10_531_size_2208_2208_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_7_23_13_91_size_1656_2208_.jpg{$}', CAST(0x0000A58101647F48 AS DateTime), NULL)

INSERT [dbo].[pic_list] ([id], [title], [summary], [picurlNew], [createtime], [sort]) VALUES (5, N'古城之夜', N'图片介绍5', N'{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_42_55_437_size_1334_1334_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_42_56_96_size_1334_1334_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_42_57_200_size_1334_1334_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_42_58_303_size_1334_1334_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_42_59_45_size_1334_1334_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_42_59_744_size_1334_1334_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_43_0_443_size_1334_1334_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_43_1_227_size_1334_1334_.jpg{$}http:///www.xxx.cn/upload/2016/1/3/2016_1_3_12_43_2_60_size_1334_1334_.jpg{$}', CAST(0x0000A58101647F48 AS DateTime), NULL)

SET IDENTITY_INSERT [dbo].[pic_list] OFF

2、实现效果：

1	挑灯夜战	图片介绍1	{$}http://www.xxx.cn/htdata/upload/2016/1/2/2016_1_2_20_53_7_965_size_1024_768.jpg{$}	2016-01-02 21:37:58.000	NULL

2	布展完毕	图片介绍2	{$}http://www.xxx.cn/htdata/upload/2016/1/3/2016_1_3_0_57_9_93_size_1334_1234_.jpg	2016-01-02 21:37:58.000	NULL

3	布展完毕	图片介绍2	{$}http://www.xxx.cn/htdata/upload/2016/1/3/2016_1_3_0_57_10_791.jpg	2016-01-02 21:37:58.000	NULL

我之前参照过http://bbs.csdn.net/topics/380210313，这个帖子

--参考:

CREATE TABLE TB(ID VARCHAR(6), COLOR NVARCHAR(30))

INSERT TB

SELECT '173160',  N'#特深蓝色,#特深蓝色' UNION ALL 

SELECT '173160',  N'#特深蓝色,#特深蓝色' UNION ALL 

SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL 

SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL 

SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL 

SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色'

GO

CREATE FUNCTION F_getStr(@color nvarchar(30))

returns nvarchar(30)

as

begin

  declare @str nvarchar(30),@temp nvarchar(30)

  set @str=''

  set @temp=''

  while charindex(',', @color+',')>0

  begin

    set @temp=left(@color, charindex(',', @color+',')-1)

    if charindex(','+@temp+',', ','+@str+',')=0

      set @str=@str+','+@temp

    set @color=stuff(@color, 1, charindex(',', @color+','), '')    

  end

  return stuff(@str, 1, 1, '')

end

go

SELECT ID,dbo.F_getStr(COLOR) as COLOR FROM TB

DROP TABLE TB

DROP FUNCTION F_getStr

/*

ID     COLOR

173160 #特深蓝色

173160 #特深蓝色

911169 #宝蓝色,#花灰色

911169 #宝蓝色,#花灰色

911169 #宝蓝色,#花灰色

911169 #宝蓝色,#花灰色

*/