/****************************************************************************************************************************
Name: ufn_SplitStringToTable
select * from [dbo].[ufn_SplitStringToTable]('123,12,1111',',')
Hisotory:
Date Name Description
***********************************************************************************************************************/
CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
@str VARCHAR(MAX) ,
@split VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
( SELECT B.id
FROM ( SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')
+ '</v>')
) A
OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N ( v )
) B
)
GO
****************************************************************************************
/****************************************************************************************************************************
Name: ufn_Split
Author:
Description: 拆分記錄
Parameters: IN - @RowData nvarchar(2000)
@SplitOn nvarchar(5)
Return: Return @RtnValue table
Calling functions: System Module
HSMCS MCST01U1
Hisotory:
Date Name Description
***********************************************************************************************************************/
CREATE FUNCTION [dbo].[ufn_Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
GO
********************************
usage:
with Data as (select data=id from dbo.ufn_SplitStringToTable(@cRFNo,@SplitOn))
Select rfr_cRFNo,soh_cSONo,skm_iSOID,skm_iProdKey
,skm_cProductID,skm_cEditionCD,skm_cImprintCD
From MOOtherRelation_moor With(nolock)
.....
.....
....
--Where rfr_cRFNo In (select data from dbo.ufn_Split(@cRFNo,@SplitOn))
Inner Join Data On data = rfr_cRFNo