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