常用函数

--判断字符

create function f_ischarandnumeric(@chr varchar(100))
returns varchar
as
begin
declare @s char(1)
declare @i int
set @i = 1
while @i <= len(@chr)
begin
select @s = substring(@chr,@i,1)
if charindex(@s,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789') = 0
return '0'
select @i = @i + 1
end
return '1'
end

create table #(chr varchar(100))
insert into #(chr)
select '222&*&*ds'
union all select '324324dsa'
union all select '&***(()()'
union all select '334343434'
union all select 'dfsessvsz'
select *,'包含字母和数字' = case dbo.f_ischarandnumeric(chr) when 1 then '√' else '' end
from #
drop table #

-- 分拆处理函数
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

/*--字符串分函数

 分拆指定分隔符的的字符串,返回结果表

--邹建 2004.04(引用请保留此信息)--*/

/*--调用示例

 SELECT * FROM f_splitSTR('aa  ba b','a ')
--*/
CREATE FUNCTION f_splitSTR(
@s varchar(8000),   --要分拆的字符串
@split varchar(10)  --数据分隔符
)RETURNS @re TABLE(id int IDENTITY(1,1), col varchar(100))
AS
BEGIN
 DECLARE @splitlen int
 SET @splitlen=LEN(@split+'a')-2
 WHILE CHARINDEX(@split,@s)>0
 BEGIN
  INSERT @re(col) VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
 END
 INSERT @re(col) VALUES(@s)
 RETURN
END
GO

-- 调用分拆函数实现数据处理

DECLARE @strPID varchar(8000)
SET @strPID = '1,5,8,10'
DECLARE @strQty varchar(8000)
SET @strQty =  '6,4,3,1'

INSERT INTO Table  (PID, Qty, Price)
SELECT PID2 = A.col, Qty2 = B.col, Price2 = C.Price
FROM dbo.f_splitSTR(@strPID, ',') A,
 dbo.f_splitSTR(@strQty, ',') B,
 Table2 C
WHERE a.id = b.id
 AND A.col = C.PID
GO

---合并函数

create table A(id int, test varchar(20))
insert  A select 1,      'MS'
union all select 1,     'SQL'
union all select 1,      'Server'
union all select 2,      'I'
union all select 2,      'am'
union all select 2,      'SQL'
union all select 2,      'OK'

create function fun(@id int)
returns varchar(1000)
as
begin
 declare @re varchar(1000)
 set @re=''
 select @re=@re+test+' ' from A where id=@id

 return @re
end

select distinct id, dbo.fun(id) as test from A

id          test                          
----------- -------------------------
1           MS SQL Server
2           I am SQL OK

例子：

有个表 sendfile
有如下字段
 docid,userid,title
  22  ,7;8   ,关于春节放假通知
  29  , 33   ,关于王平的处罚通告

要求:
 当往表 sendfile 里插入数据 或者更新 数据的时候,

要把 里面的3个字段的 数据写入 sendfile_full表中

sid,docid,userid,title  其中 SID为自增长.

注意:  userid 里面 的 userid  可能存储2个或者2个以上的用户ID,用;割开

这就意味着, 当我往 sendfile里插入

 22, 7;8 ,关于春节放假通知

这样的数据,那么 在 sendfile_full表中 就应该写入2条记录:

sid,docid,userid,title
2  , 22  , 7    ,关于春节放假通知
3  , 22  , 8    ,关于春节放假通知

--环境
create table tab
(
docid int,
userid varchar(50),
title varchar(100)
)

create table sendfile_full
(
sid int identity(1,1),
docid int,
userid varchar(50),
title varchar(100)
)

--建立辅助表
SELECT TOP 8000 id = identity(int,1,1)
INTO num FROM syscolumns a, syscolumns b  

--建立触发器
create trigger t_tab on tab
for update,insert
as
begin
  if exists (select 1 from deleted)
  begin
    delete from sendfile_full where docid in (select docid from deleted)
  end
 
  insert into sendfile_full(docid,userid,title)
  SELECT A.docid,userid = SUBSTRING(A.userid, B.ID, CHARINDEX(';', A.userid + ';', B.ID) - B.ID),title
  FROM inserted A, num B
  WHERE SUBSTRING(';' + a.userid, B.id, 1) = ';'  

end

--插入数据
insert into tab select 22  ,'7;8'   ,'关于春节放假通知'
--查询
select * from sendfile_full
1 22 7 关于春节放假通知
2 22 8 关于春节放假通知
--更新数据
update tab set userid = '33'
--查询
select * from sendfile_full
3 22 33 关于春节放假通知 

--删除环境
drop table tab
drop table sendfile_full

create table ta(docid int,userid varchar(50),title varchar(20))
create table tb(r_id int identity(1,1),docid int,userid varchar(50),title varchar(20))

--truncate table ta
select * from ta
select * from tb

create trigger test_tr on ta
for insert,update--定义插入、更新都触发
as
begin
DECLARE @docid int,@userid varchar(50),@title varchar(20)
DECLARE roy CURSOR
FOR SELECT * from inserted
OPEN roy
FETCH next FROM roy
into @docid,@userid,@title
WHILE @@FETCH_STATUS = 0
BEGIN
  begin
  while CHARINDEX(';',@userid)>0
    begin
      INSERT   tb   select @docid,LEFT(@userid,CHARINDEX(';',@userid)-1) ,@title
      SET   @userid=STUFF(@userid,1,CHARINDEX(';',@userid),'')  
    end
   insert tb select @docid,@userid,@title
  end
   FETCH NEXT FROM roy
   into @docid,@userid,@title
END
CLOSE roy
DEALLOCATE roy
end

--测试
insert ta
select  22  ,'7;8'   ,'关于春节放假通知' union all
select  29  , '33'   ,'关于王平的处罚通告'
--查询
select * from tb

r_id        docid       userid                                             title               
----------- ----------- -------------------------------------------------- --------------------
1           22          7                                                  关于春节放假通知
2           22          8                                                  关于春节放假通知
3           29          33                                                 关于王平的处罚通告

（所影响的行数为 3 行）