SQL字符串分割成若干列

在数据库编程中，很多朋友会碰到分割字符串的需求，一般都是分割成一列多行模式，但也有时会需要分割成多列一行的模式，下面我们来看下如何实现这种需求。

 

首先创建一个辅助函数，来得到生成多列的SQL语句：

create function toArray( @str nvarchar(1000), @sym nvarchar(10) ) returns nvarchar(3000) as begin set @str=rtrim(ltrim(@str)) declare @i int set @i=charindex(@sym,@str COLLATE Chinese_PRC_CS_AS_WS) declare @sql nvarchar(1000) set @sql='select ' declare @ind int=1 while @i>=1 begin set @sql=@sql+''''+left(@str,@i-1)+''' col'+CONVERT(varchar,@ind,10)+',' set @str=substring(@str,@i+1,len(@str)-@i) set @i=charindex(@sym,@str COLLATE Chinese_PRC_CS_AS_WS) set @ind=@ind+1 end if @str<>'' set @sql=@sql+''''+@str+''' col'+CONVERT(varchar,@ind,10)+',' set @sql=LEFT(@sql,LEN(@sql)-1) return @sql end

关于这个函数，应该不难理解，这里不再啰嗦

 

有了生成多列的SQL语句，应该如何去利用它呢？我们很自然的想到了临时表，将数据插入临时表不就可以用了么！

但是，临时表有两种：本地临时表和全局临时表，本地临时表的使用是受限制的，它只对当前会话起作用，我们要使用它，只能把要操作的SQL语句一起拼接到函数生成的SQL中：

declare @sql nvarchar(3000) set @sql=dbo.toArray('11-21','-')+' into #tbarr' set @sql=@sql+' select a.* from tbn a inner join #tbarr b on a.cid=b.col1 and a.bid=b.col2' set @sql=@sql+' drop table #tbarr' exec sp_executesql @sql

但是拼接SQL语句是我们应该尽量避免的思路，除非不得以，我们不用。

 

下面我们看下如何通过常规语句实现这种功能

declare @sql nvarchar(3000) set @sql=dbo.toArray('11-21','-')+' into ##tbarr' exec sp_executesql @sql select a.* from tbn a inner join ##tbarr b on a.cid=b.col1 and a.bid=b.col2 drop table ##tbarr

这样不可避免的我们使用了全局表，这也是我们应该尽量避免的编程思路，唉没办法，总是不能十全十美：（

 

也许很多朋友不理解，这样做有什么意义呢？具体要怎样的需求才会用到这个方案？

举个例子：

商品表(product)的结构是这样的：

主键 PID

类别 CID

品牌 BID

...

现在我们要查询类别ID为11品牌ID为21的所有商品，但是我们只得到了类别ID+品牌ID的字符串，那么应该如何查处对应的商品信息呢？

看示例代码：

create table product ( pid int identity(1,1), cid int, bid int, title varchar(100), dtime datetime default getdate() ) insert into product(cid,bid,title) select 10,20,'aaa' union select 11,21,'bbb' union select 12,22,'ccc' union select 13,23,'ddd' union select 14,24,'eee' go declare @sql nvarchar(3000) set @sql=dbo.toArray('11-21','-')+' into ##tbarr' --如果系统中存在表##tbarr，则删除 if exists(select name from sysobjects where name='##tbarr'and type='u') begin waitfor delay '00:00:01'--等待1秒钟 if exists(select name from sysobjects where name='##tbarr'and type='u') drop table ##tbarr end exec sp_executesql @sql select a.* from product a inner join ##tbarr b on a.cid=b.col1 and a.bid=b.col2 drop table ##tbarr

结果如下：

 

现在这个需求还比较简单，只有2个ID，如果需要判断多个呢？字符串这样“11-21-32-23” 这样一来使用这个解决方案的优点就显而易见了。

 

作者：朱会震