笔记249 SQLSERVER自定义函数实现split功能 2013-4-25

 1 --SQLSERVER自定义函数实现split功能 2013-4-25
 2 --http://blog.chinaunix.net/uid-584462-id-2733085.html
 3 --http://social.technet.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/29724c5a-adb6-4a2c-9003-86ead071c059
 4 
 5 USE [tempdb]
 6 GO
 7 -------------------------------------------------
 8 --实现split功能 的函数
 9 --date :2005-4-20
10 --Author :Domino
11 ---------------------------------------------------
12 DROP FUNCTION [dbo].[f_split]
13 GO
14 CREATE FUNCTION f_split
15 (
16   @SourceSql VARCHAR(8000) ,
17   @StrSeprate VARCHAR(10)
18 )
19 RETURNS @temp TABLE ( str VARCHAR(100) )
20 AS
21     BEGIN
22         DECLARE @i INT
23         SET @SourceSql = RTRIM(LTRIM(@SourceSql))
24         SET @i = CHARINDEX(@StrSeprate, @SourceSql)
25         WHILE @i >= 1
26             BEGIN
27                 INSERT  @temp
28                 VALUES  ( LEFT(@SourceSql, @i - 1) )
29                 SET @SourceSql = SUBSTRING(@SourceSql, @i + 1,
30                                            LEN(@SourceSql) - @i)
31                 SET @i = CHARINDEX(@StrSeprate, @SourceSql)
32             END
33         IF @SourceSql <> '\'
34             INSERT  @temp
35             VALUES  ( @SourceSql )
36         RETURN
37     END
38 --实验：
39 USE tempdb
40 go
41 CREATE TABLE  #tempA (ID varchar(20),str varchar(20))
42 INSERT INTO #tempA
43 SELECT '1001','A,B,C,D'
44 
45 
46 DECLARE @str VARCHAR(50)
47 DECLARE @ID INT
48 SELECT @str=a.[str],@ID=A.[ID] FROM #tempA AS a
49 
50 SELECT  @ID AS ID,*   FROM dbo.[f_split](@str,',')