--sql server 2005 输出表的函數用法
--Geovin Du 塗聚文
--締友計算機信息技術有限公司
create function EmailParse (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
declare @i int
select @i = charindex('@', @email,1);
if (@i > 1)
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
else
insert into @t values (null,null)
return
end
--測試
select username, domain
from dbo.EmailParse('geovindu@dupcit.com')
--建表測試
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[emails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [emails]
GO
create table emails (eID int primary key, EmailAddress varchar(100))
insert into emails
select 1,'geovindu@geovi.com' union all
select 2,'geovindu@dusystem.com' union all
select 3,'geovindu@hotmail.com'
GO
--SQL Server 2005
select
emails.eID,emails.EmailAddress, s.Username, s.Domain
from
emails
cross apply
dbo.EmailParse(emails.EmailAddress) s
---SQL Server 2005
select
emails.eID, s.Username, s.Domain
from
emails
outer apply
dbo.EmailParse(emails.EmailAddress) s
sql server 2005 输出表的函數用法
最新推荐文章于 2019-12-04 23:15:13 发布