Alter function DBO. udf_splitsubstring
(
@ Expression varchar (8000)
, @ Delimiter varchar (100)
, @ Int
)
Returns varchar (8000)
As
Begin
-- Declare @ expression varchar (8000)
-- Set @ expression = 'abc @ PPPP @'
-- Declare @ delimiter varchar (100)
-- Set @ delimiter = '@@@'
-- Declare @ int
-- Set @ = 3
Declare @ P int
Set @ P = charindex (@ delimiter, @ expression)
If @ P> 0
Begin
Set @ P = @ P + Len (@ delimiter)-1
End
Declare @ I int
Set @ I = 1
While @ I <@
Begin
Set @ I = @ I + 1
Set @ expression = substring (@ expression, @ p + 1, Len (@ expression)-@ P)
Set @ P = charindex (@ delimiter, @ expression)
If @ P> 0
Begin
Set @ P = @ P + Len (@ delimiter)-1
End
Else
Begin
Break
End
End
Declare @ s varchar (1000)
If @ p = 0 and @ I = @
Begin
Set @ s = @ expression
End
Else if @ I = @
Begin
Set @ s = substring (@ expression, 1, @ p-len (@ delimiter ))
End
Return @ s
End
Go
Declare @ varchar (1000)
Set @ = '@ playyuer @ WWW &. microshaoft & com@90.90 @ PPPP @.'
Declare @ d varchar (1000)
Set @ d = '@@'
Select
DBO. udf_splitsubstring (@, @ D, 0)
, DBO. udf_splitsubstring (@, @ d, 1)
, DBO. udf_splitsubstring (@, @ D, 2)
, DBO. udf_splitsubstring (@, @ D, 3)
, DBO. udf_splitsubstring (@, @ D, 4)
, DBO. udf_splitsubstring (@, @ d, 5)
, DBO. udf_splitsubstring (@, @ D, 6)
, DBO. udf_splitsubstring (@, @ d, 7)
, DBO. udf_splitsubstring (@, @ D, 8)
, DBO. udf_splitsubstring (@, @ D, 100)