sql server 2005输出表的函 用法
Sql代码 www.2cto.com
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('geovindu@dupcit.com')
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,'geovindu@geovi.com' union all
31 select 2,'geovindu@dusystem.com' union all
32 select 3,'geovindu@hotmail.com'
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