比較兩個字符串之間的相同與不同之處

/****** Object:  UserDefinedFunction [dbo].[getDiffterString]    Script Date: 04/11/2011 10:47:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  讓
-- Create date: 2011-03-23
-- Description: 比較兩具字符串之間的相同與不同之處
-- Debug:select * from dbo.getDiffterString('BC','ABC:BC:C:D:E',':')  or select * from Fun_Split('a,b,c',',')
-- 修改時間:      2011-03-23
--修改人:        lz_wu@maxense.com
-- =============================================
CREATE function [dbo].[getDiffterString]
(
   @orginStr varchar(4000),--第一個要比較的字符串
   @newStr varchar(4000),--第二個要比較的字符串
   @speaterStr varchar(20)--分隔的字符
)
returns @difTable table(sameStr varchar(4000),orginDifferStr varchar(4000),newDiffterStr varchar(4000))
as
begin
/*******************************************/
declare @i int,@helpStr varchar(4000),@IsExec tinyint,@saveSameStr varchar(4000),@saveNewStr varchar(4000)
declare @SingleStr varchar(2000)
set @IsExec=0--
set @saveSameStr=''
set @orginStr=ltrim(rtrim(ISNULL(@orginStr,'')))--如果為null值,則把它設為空
set @newStr=ltrim(rtrim(ISNULL(@newStr,'')))----如果為null值,則把它設為空
set @speaterStr=LTRIM(RTRIM(isnull(@speaterStr,'')))----如果為null值,則把它設為空
if @speaterStr='' --如果分割符為空
   begin
     insert into @difTable select '',@orginStr,@newStr
     return
   end
if @IsExec>0--去除多余的分割符
 begin
    gotoskip:
    set @saveSameStr=''
    if RIGHT(@helpStr,len(@speaterStr))<>@speaterStr
       set @helpStr=@helpStr+@speaterStr
    set @i=CHARINDEX(@speaterStr,@helpStr)
    while @i>0
      begin
        set @SingleStr=rtrim(ltrim(left(@helpStr,@i-1)))
        if @SingleStr=''
          begin
            SET @helpStr=substring(@helpStr,@i+1,len(@helpStr)-@i)
            SET @i=charindex(@speaterStr,@helpStr)
            continue
          end
          SET @helpStr=substring(@helpStr,@i+1,len(@helpStr)-@i)
          set @helpStr=REPLACE(@speaterStr+@helpStr+@speaterStr,@speaterStr+@SingleStr+@speaterStr,@speaterStr)--去除相同的內容
          set @saveSameStr=@saveSameStr+@SingleStr+@speaterStr
          SET @i=charindex(@speaterStr,@helpStr)
      end
      if right(@saveSameStr,len(@speaterStr))=@speaterStr
           set @saveSameStr=left(@saveSameStr,len(@saveSameStr)-len(@speaterStr))
      if left(@saveSameStr,len(@speaterStr))=@speaterStr
           set @saveSameStr=substring(@saveSameStr,len(@speaterStr)+1,len(@saveSameStr))
      if @IsExec=1--表示其中有一個字符串為空@orginStr與@newStr
         begin
            insert into   @difTable select '',(case when @orginStr='' then  '' else @saveSameStr end),(case when @newStr='' then  '' else @saveSameStr end)
            return
         end
      if @IsExec=2--表示其中一個字串符不存在分割符@orginStr與@newStr
         begin
            if @saveNewStr=''--表示不存在相同的
               begin
                insert into @difTable select '',(case when CHARINDEX(@speaterStr,@orginStr)=0 then @orginStr else @saveSameStr end),(case when  CHARINDEX(@speaterStr,@newStr)=0 then @newStr else @saveSameStr end)
                return
               end
             else
               begin
                 insert into @difTable select @saveNewStr,(case when @orginStr='' then '' else @saveSameStr end),(case when @newStr='' then '' else @saveSameStr end)
                 return
               end
         end
      if @IsExec=3
        begin
            set @orginStr=@saveSameStr
            set @helpStr=@saveNewStr
            set @IsExec=4
            goto gotoskip
        end
      if @IsExec=4
        begin
           insert into @difTable select @newStr,@orginStr,@saveSameStr
           return
        end
 end
if @orginStr='' or @newStr='' --只要其中一個字符串為空就返回
  begin
      set @helpStr=(case when @orginStr='' then @newStr else @orginStr end)
      set @IsExec=1
      goto gotoskip
  end
--insert  into @difTable select 'aa','bb','cc'
set @orginStr=LTRIM(RTRIM(@orginStr))--表示兩個字符串都不為空
set @newStr=LTRIM(RTRIM(@newStr))--表示兩個字符串都不為空
if CHARINDEX(@speaterStr,@newStr)=0 or CHARINDEX(@speaterStr,@orginStr)=0--表示其中一個字符串不存在分隔字符串的判斷
   begin
      if CHARINDEX(@speaterStr,@newStr)=0 and CHARINDEX(@speaterStr,@orginStr)=0--表示兩個字符串都不存在分割符
      begin
            if @newStr=@orginStr
            begin
             set @saveSameStr=@newStr
             set @orginStr=''
             set @newStr=''
            end
            else
              set @saveSameStr=''
            insert into @difTable select @saveSameStr,@orginStr,@newStr
            return
      end
      set @saveNewStr=(case when CHARINDEX(@speaterStr,@newStr)=0 then @orginStr else @newStr end)
      set @saveSameStr=(case when CHARINDEX(@speaterStr,@newStr)=0 then @newStr else @orginStr end)
      if right(@saveNewStr,len(@speaterStr))<>@speaterStr--最右邊添加分割符
          set @saveNewStr=@saveNewStr+@speaterStr
      if left(@saveNewStr,len(@speaterStr))<>@speaterStr--最左邊添加分割符
          set @saveNewStr=@speaterStr+@saveNewStr
      if CHARINDEX(@speaterStr+@saveSameStr+@speaterStr,@saveNewStr)=0--表示不存在相同的內容
         set @saveSameStr=''
      else
         begin
             set @saveNewStr=REPLACE(@saveNewStr,@speaterStr+@saveSameStr+@speaterStr,@speaterStr)
             while CHARINDEX(@speaterStr+@saveSameStr+@speaterStr,@saveNewStr)>0
                set @saveNewStr=REPLACE(@saveNewStr,@speaterStr+@saveSameStr+@speaterStr,@speaterStr)
             if @saveSameStr=@newStr
                 set @newStr=''
             else
                 set @orginStr=''
         end
      set @helpStr=@saveNewStr
      set @saveNewStr=@saveSameStr--保存相同的內容
      set @IsExec=2
      goto gotoskip
   end
else
   begin
       if right(@orginStr,len(@speaterStr))<>@speaterStr--最右邊添加分割符
          set @orginStr=@orginStr+@speaterStr
       if left(@orginStr,len(@speaterStr))<>@speaterStr--最左邊添加分割符
          set @orginStr=@speaterStr+@orginStr
       if right(@newStr,len(@speaterStr))<>@speaterStr--添加一個分割符以便循環時能讀取到最后一個字符串
          set @newStr=@newStr+@speaterStr
       set @saveNewStr=@newStr
      if left(@saveNewStr,len(@speaterStr))<>@speaterStr--最左邊添加分割符
          set @saveNewStr=@speaterStr+@saveNewStr
       set @i=charindex(@speaterStr,@newStr)
       while @i>0
         begin
           set @helpStr=rtrim(ltrim(left(@newStr,@i-1)))--保存當次循環的值
           if @helpStr=''--如果為空值則進行下一次循環
              begin
                  SET @newStr=substring(@newStr,@i+1,len(@newStr)-@i)
                  SET @i=charindex(@speaterStr,@newStr)
                 continue
              end--追加代碼end
            if charindex(@speaterStr+@helpStr+@speaterStr,@orginStr)>0--表示存在相同的內容
              begin
                 set @saveSameStr=@saveSameStr+@helpStr+@speaterStr--存在相同的內容則保存起來
                 set @orginStr=replace(@orginStr,@speaterStr+@helpStr+@speaterStr,@speaterStr)--存在相同的內容則替換成分割符
                 while CHARINDEX(@speaterStr+@helpStr+@speaterStr,@orginStr)>0
                       set @orginStr=replace(@orginStr,@speaterStr+@helpStr+@speaterStr,@speaterStr)
                 set @saveNewStr=replace(@speaterStr+@saveNewStr,@speaterStr+@helpStr+@speaterStr,@speaterStr)--存在相同的內容則替換成分割符
                 while CHARINDEX(@speaterStr+@helpStr+@speaterStr,@saveNewStr)>0
                     set @saveNewStr=replace(@speaterStr+@saveNewStr,@speaterStr+@helpStr+@speaterStr,@speaterStr)
              end
             SET @newStr=substring(@newStr,@i+1,len(@newStr)-@i)
             SET @i=charindex(@speaterStr,@newStr)
         end
         if right(@saveSameStr,len(@speaterStr))=@speaterStr
            set @saveSameStr=left(@saveSameStr,len(@saveSameStr)-len(@speaterStr))
         if left(@saveSameStr,len(@speaterStr))=@speaterStr
            set @saveSameStr=substring(@saveSameStr,len(@speaterStr)+1,len(@saveSameStr)) 
         set @newStr=@saveSameStr--保存相同的內容
         set @helpStr=@orginStr--第一個字符串(@saveNewStr為第二個字符串)
         set @IsExec=3
         goto gotoskip
   end
  return
/***********************************************/
end

GO