sql server 2005 输出表的函數用法

view sourceprint?
01 --sql server 2005 输出表的函數用法  

02 --Geovin Du 塗聚文   

03 --締友計算機信息技術有限公司  

04    

05 create function EmailParse (@email varchar(1000))  

06 returns @t table (UserName varchar(20), Domain varchar(20))  

07 as 

08 begin 

09  declare @i int 

10    

11  select @i = charindex('@', @email,1);  

12    

13  if (@i > 1)  

14   insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))  

15  else 

16   insert into @t values (null,null)  

17    

18  return  

19 end 

20 --測試  

21 select username, domain   

22 from   dbo.EmailParse('[e-mail]')  

23 --建表測試  

24 IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[emails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)   

25 DROP TABLE [emails]   

26 GO  

27 create table emails (eID int primary key, EmailAddress varchar(100))  

28    

29 insert into emails  

30 select 1,'[e-mail]' union all 

31 select 2,'[e-mail]' union all 

32 select 3,'[e-mail]' 

33 GO  

34 --SQL Server 2005   

35 select  

36  emails.eID,emails.EmailAddress, s.Username, s.Domain  

37 from  

38  emails  

39 cross apply   

40 dbo.EmailParse(emails.EmailAddress) s  

41 ---SQL Server 2005  

42 select  

43  emails.eID, s.Username, s.Domain  

44 from  

45  emails  

46 outer apply    

47 dbo.EmailParse(emails.EmailAddress) s