vnsc5858威尼斯城官网:sql split函数二款实例

时间:2020-03-11 20:20来源:计算机教程
--使用程序块 --Title:生成測試數據 --Author:wufeng4552 --Date :2009-09-2113:40:59 declare @s varchar(8000) set @s=lower(@@version) select @s /*microsoft sql server 2005 - 9.00.4035.00 (intel x86) nov 24 200813:01:59 copyright (c)

--使用程序块 --Title:生成測試數據 --Author:wufeng4552 --Date :2009-09-21 13:40:59 declare @s varchar(8000) set @s=lower(@@version) select @s /* microsoft sql server 2005 - 9.00.4035.00 (intel x86) nov 24 2008 13:01:59 copyright (c) 1988-2005 microsoft corporation enterprise edition on windows nt 5.2 (build 3790: service pack 2) (1 個資料列受到影響) */ declare @i int,@j int select @i=1,@j=len(@j) while charindex(' ',' ' @s,@i)0 begin set @I=charindex(' ',' ' @s,@i) 1 if @i@j continue set @s=stuff(@s,@i-1,1,upper(substring(@s,@i-1,1))) end select @s /* Microsoft Sql Server 2005 - 9.00.4035.00 (intel X86) nov 24 2008 13:01:59 copyright (c) 1988-2005 Microsoft Corporation enterprise Edition On Windows Nt 5.2 (build 3790: Service Pack 2) (1 個資料列受到影響) */ ----使用函数 --Title:生成測試數據 --Author:wufeng4552 --Date :2009-09-21 13:40:59 if object_id('F_split')is not null drop function dbo.F_split go create function F_split(@s nvarchar(1000)) returns nvarchar(1000) as begin declare @str nvarchar(1000),@split nvarchar(100) select @s=@s ' ',@str='' while charindex(' ',@s)0 begin set @split=left(@s,charindex(' ',@s)) set @str=@str upper(left(@split,1)) right(@split,len(@split)) set @s=stuff(@s,1,charindex(char(32),@s),'') end return @str end go declare @s varchar(1000) set @s=lower(@@version) select dbo.F_split(@s) /* Microsoft Sql Server 2005 - 9.00.4035.00 (intel X86) nov 24 2008 13:01:59 copyright (c) 1988-2005 Microsoft Corporation enterprise Edition On Windows Nt 5.2 (build 3790: Service Pack 2) */ --3借住系統表,或臨時表 --Title:生成測試數據 --Author:wufeng4552 --Date :2009-09-21 13:40:59 declare @str varchar(1000) select @str=char(32) lower(@@version) select @str=replace(@str,char(32) char(number),char(32) char(number)) from master..spt_values where type='p' and number between 65 and 90 select stuff(@str,1,1,'') /* Microsoft Sql Server 2005 - 9.00.4035.00 (intel X86) nov 24 2008 13:01:59 copyright (c) 1988-2005 Microsoft Corporation enterprise Edition On Windows Nt 5.2 (build 3790: Service Pack 2) (1 個資料列受到影響) */

分割再成批插入

declare @s varchar(8000),@sql nvarchar(4000)set @s='1,12,1212,4545'set @sql='insert into t(col) select ' replace(@s,',',' col union all select ')exec(@sql)

vnsc5858威尼斯城官网,测试drop table #tabledeclare @s varchar(8000),@sql nvarchar(4000)set @s='1,12,1212,4545,454'create table #table (col int)set @sql='insert into #table(col) select ' replace(@s,',',' col union all select ')exec(@sql)select * from #table

create function f_split(@sourcesql varchar(8000),@strseprate varchar(10))returns @temp table(a varchar(100))--实现split功能 的函数as begindeclare @i intset @sourcesql=rtrim(ltrim(@sourcesql))set @i=charindex(@strseprate,@sourcesql)while @i=1begininsert @temp values(left(@sourcesql,@i-1))set @sourcesql=substring(@sourcesql,@i 1,len(@sourcesql)-@i)set @i=charindex(@strseprate,@sourcesql)endif @sourcesql''insert @temp values(@sourcesql)return end

用法:select * from dbo.f_split('abc:bc:c:d:e',':')

编辑:计算机教程 本文来源:vnsc5858威尼斯城官网:sql split函数二款实例

关键词: