The difference between two strings

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

SET QUOTED_IDENTIFIER ON
GO

-- ===================================================== ======
-- Author: Let
-- Create date: 2011-03-23
-- Description: The difference between two strings
-- Debug: select * from dbo. getDiffterString ('bc', 'abc: BC: C: D: e', ':') or select * from Fun_Split ('a, B, C ',',')
-- Modification time:
-- Author: lz_wu@maxense.com
-- ===================================================== ======
CREATE function [dbo]. [getDiffterString]
(
@ OrginStr varchar (4000), -- the first string to be greater than the limit
@ NewStr varchar (4000), -- the second string is better than the limit
@ SpeaterStr varchar (20) -- delimiter
)
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, '') -- if it is null, set it to null.
Set @ newStr = ltrim (rtrim (ISNULL (@ newStr, '') ---- if it is null, set it to null.
Set @ speaterStr = LTRIM (RTRIM (isnull (@ speaterStr, '') ---- if it is null, set it to null.
If @ speaterStr = ''-- if the Delimiter is null
Begin
Insert into @ difTable select '', @ orginStr, @ newStr
Return
End
If @ isexec> 0 -- Remove unnecessary delimiters
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) -- remove the same content
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 -- indicates that one of the strings is null @ orginStr and @ 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 -- indicates that one of the strings does not have a separator @ orginstr and @ newstr.
Begin
If @ savenewstr = ''-- indicates that the same
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 ='' -- returns if one of the strings is null.
Begin
Set @ helpStr = (case when @ orginStr = ''then @ newStr else @ orginStr end)
Set @ IsExec = 1
Goto gotoskip
End
-- Insert into @ difTable select 'A', 'bb ', 'cc'
Set @ orginStr = LTRIM (RTRIM (@ orginStr) -- indicates that both strings are not empty
Set @ newStr = LTRIM (RTRIM (@ newStr) -- indicates that both strings are not empty.
If CHARINDEX (@ speaterStr, @ newStr) = 0 or CHARINDEX (@ speaterStr, @ orginStr) = 0 -- indicates that one of the strings does not have a separator string.
Begin
If CHARINDEX (@ speaterStr, @ newStr) = 0 and CHARINDEX (@ speaterStr, @ orginStr) = 0 -- indicates that neither string has a separator.
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 -- add the delimiter to the rightmost Vertex
Set @ saveNewStr = @ saveNewStr + @ speaterStr
If left (@ saveNewStr, len (@ speaterStr) <> @ speaterStr -- add delimiter to the leftmost delimiter
Set @ saveNewStr = @ speaterStr + @ saveNewStr
If CHARINDEX (@ speaterStr + @ saveSameStr + @ speaterStr, @ saveNewStr) = 0 -- indicates that the same content does not exist.
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 -- save the same content
Set @ isexec = 2
Goto gotoskip
End
Else
Begin
If right (@ orginstr, Len (@ speaterstr) <> @ speaterstr -- add the delimiter to the rightmost Vertex
Set @ orginstr = @ orginstr + @ speaterstr
If left (@ orginstr, Len (@ speaterstr) <> @ speaterstr -- add delimiter to the leftmost delimiter
Set @ orginstr = @ speaterstr + @ orginstr
If right (@ newStr, len (@ speaterStr) <> @ speaterStr -- add a delimiter so that the last string can be obtained in the cycle.
Set @ newStr = @ newStr + @ speaterStr
Set @ saveNewStr = @ newStr
If left (@ saveNewStr, len (@ speaterStr) <> @ speaterStr -- add delimiter to the leftmost delimiter
Set @ saveNewStr = @ speaterStr + @ saveNewStr
Set @ I = charindex (@ speaterStr, @ newStr)
While @ I> 0
Begin
Set @ helpStr = rtrim (ltrim (left (@ newStr, @ i-1) -- save the value of the current cycle
If @ helpstr = ''-- if it is null, the next cycle is followed.
Begin
Set @ newstr = substring (@ newstr, @ I + 1, Len (@ newstr)-@ I)
Set @ I = charindex (@ speaterstr, @ newstr)
Continue
End -- append to end
If charindex (@ speaterstr + @ helpstr + @ speaterstr, @ orginstr)> 0 -- indicates that the same content exists.
Begin
Set @ savesamestr = @ savesamestr + @ helpstr + @ speaterstr -- save the same content
Set @ orginstr = Replace (@ orginstr, @ speaterstr + @ helpstr + @ speaterstr, @ speaterstr) -- if the same content exists, it is replaced by a delimiter.
While charindex (@ speaterstr + @ helpstr + @ speaterstr, @ orginstr)> 0
Set @ orginstr = Replace (@ orginstr, @ speaterstr + @ helpstr + @ speaterstr, @ speaterstr)
Set @ savenewstr = Replace (@ speaterstr + @ savenewstr, @ speaterstr + @ helpstr + @ speaterstr, @ speaterstr) -- if the same content exists, it is replaced by a delimiter.
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 -- save the same content
Set @ helpstr = @ orginstr -- the first string (@ savenewstr is the second string)
Set @ isexec = 3
Goto gotoskip
End
Return
/*************************************** ********/
End

Go