sql 转换 自定义函数

USE [your db]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_ConvertLogTypes]    Script Date: 2015/12/7 11:21:20 ******/
DROP FUNCTION [dbo].[fn_ConvertLogTypes]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_ConvertLogTypes]    Script Date: 2015/12/7 11:21:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



/*

	declare @LogTypes varchar(1000)='1,2,5'
	select * from [dbo].[fn_ConvertLogTypes](@LogTypes)

	declare @LogTypes varchar(1000)=''
	select * from [dbo].[fn_ConvertLogTypes](@LogTypes)
	 
	declare @LogTypes varchar(1000)=null
	select * from [dbo].[fn_ConvertLogTypes](@LogTypes)
	 

*/

create function [dbo].[fn_ConvertLogTypes]( @LogTypes varchar(1000))
returns @TblLogTypes table (Value varchar(1000), ValueDeleted varchar(1000) )  
as begin
	
	declare @LogTypes_value varchar(1000)
	declare @LogTypes_valueDeleted varchar(1000)

	--declare @LogTypes varchar(1000)='["bp","bw","bg","lab","survey"]'
	declare @dicLogType TABLE(ID int identity,Value varchar(20))

	insert into @dicLogType
	select 'bg'
	union all select 'bp' 
	union all select 'dose'
	union all select 'meal' 
	union all select 'exercise' 
	union all select 'bw' 
	union all select 'symptom'
	union all select 'lab' 
	union all select 'survey'
	union all select 'fever'
 
	--set @LogTypes=replace(@LogTypes,'[','')
	--set @LogTypes=replace(@LogTypes,']','')
	--set @LogTypes=replace(@LogTypes,'"','')
 

	--select a.* from @dicLogType a where Value NOT IN
	--(
	--	select b.Value from fn_SplitStringToList(@LogTypes,',') B
	--) 

	declare @t_value table(Value varchar(20))
	insert into @t_value
	select a.Value from @dicLogType a where ID IN
	(
		select b.Value from fn_SplitStringToList(@LogTypes,',') B
	) 

	declare @t_valueDeleted table(Value varchar(20))
	insert into @t_valueDeleted
	select a.Value from @dicLogType a where ID not IN
	(
		select b.Value from fn_SplitStringToList(@LogTypes,',') B
	) 

	--SELECT  data=STUFF((SELECT ','+[value] FROM @t t FOR XML PATH('')), 1, 1, '')


	SELECT  @LogTypes_value=STUFF((SELECT ','+[value] FROM @t_value t FOR XML PATH('')), 1, 1, '')
	select @LogTypes_value=replace(@LogTypes_value,',','","')
	select @LogTypes_value='["' + @LogTypes_value + '"]'

	SELECT  @LogTypes_valueDeleted=STUFF((SELECT ','+[value] FROM @t_valueDeleted t FOR XML PATH('')), 1, 1, '')
	select @LogTypes_valueDeleted=replace(@LogTypes_valueDeleted,',','","')
	select @LogTypes_valueDeleted='["' + @LogTypes_valueDeleted + '"]'
 
	insert into @TblLogTypes
	select @LogTypes_value as Value, @LogTypes_valueDeleted as ValueDeleted

	return;

end




GO

USE [your db]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_SplitStringToList]    Script Date: 2015/12/7 13:15:47 ******/
DROP FUNCTION [dbo].[fn_SplitStringToList]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_SplitStringToList]    Script Date: 2015/12/7 13:15:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[fn_SplitStringToList](
	@DataString [nvarchar](max),
	@Separator [nvarchar](max)
)
RETURNS @DataStringsTable TABLE (
	[Id] int identity(1,1),
	[Value] nvarchar(max)
 )
AS
BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     DECLARE @ReturnText nvarchar(max);
     SELECT @CurrentIndex=1;
     WHILE(@CurrentIndex<=len(@DataString))
         BEGIN
             SELECT @NextIndex=charindex(@Separator,@DataString,@CurrentIndex);
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@DataString)+1;
                 SELECT @ReturnText=substring(@DataString,@CurrentIndex,@NextIndex-@CurrentIndex);
                 INSERT INTO @DataStringsTable([Value]) VALUES(@ReturnText);
                 SELECT @CurrentIndex=@NextIndex+1;
             END
     RETURN;
END

GO

运行结果：