Function For Return Table

CREATE FUNCTION dbo.GetResultSetFromString
 ( @InputString NVARCHAR(4000))

RETURNS @retRows TABLE (
 IndexString NVARCHAR(50) COLLATE DATABASE_DEFAULT,
 Priority TINYINT
 )

 AS 

BEGIN

DECLARE @index INT
DECLARE @Word NVARCHAR(4000)
DECLARE @CurrentWord VARCHAR(255)
DECLARE @Separator NVARCHAR(5)
DECLARE @LoopIndex TINYINT

SET @Separator = ' '
SET @index = 1
SET @Word = @InputString
SET @LoopIndex = 1
WHILE LTrim(RTrim(@WOrd)) != ''

 BEGIN
  SET @index = CHARINDEX ( @Separator , @Word , 1 )
 
  IF @index = 0
  BEGIN
   
   SET @index = Len(@Word) + 1
 
  END
 
  SET @CurrentWord = LEFT(@Word,@index - 1)
 
  SET @Word = SubString(@Word,@index+1,Len(@Word))
  
  IF LTrim(RTrim(@CurrentWord)) != ''
  BEGIN
   INSERT INTO @retRows (
    IndexString,
    Priority
   ) VALUES (
    @CurrentWord,
    @LoopIndex
   )
  END
 SET @LoopIndex = @LoopIndex + 1

 END
 
 RETURN
 
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION  dbo.boaUpLow (@String VARCHAR(2000)) RETURNS VARCHAR(2000)
AS
 
BEGIN
 DECLARE @i INTEGER
 DECLARE @j INTEGER
 DECLARE @Result VARCHAR(2000)
 DECLARE @Upper AS INTEGER
 DECLARE @Find AS VARCHAR(2000)

 SET @j = 1
 SET @i= 1
 SET @Result =''
 SET @Find = ' -&()./_' /* Add to this string any character you want to eliminate */
 SET @Upper = 1

 WHILE(@i<=len(@String))

  BEGIN
   SET @j=1
   IF (@Upper = 1 )
    SET @Result = @Result + upper(Substring(@String,@i,1))
   ELSE
    SET @Result = @Result + Lower(Substring(@String,@i,1))
   /* End */

   SET @Upper = 0

   WHILE(@j<=len(@find))
    BEGIN
     IF  substring(@String,@i,1) = substring(@Find,@j,1)
       SET @Upper = 1 
     SET @j=@j+1
    END
   
   SET @i=@i+1
  
  END
 
 RETURN (@Result)
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.EscapeSingleQuotes (
 @String NVARCHAR(4000)
)
RETURNS NVARCHAR(4000) AS
BEGIN
 
 RETURN REPLACE(@String, '''', '''''')
 
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

转载于:https://www.cnblogs.com/zhangchenliang/archive/2010/01/18/1650917.html