SQL Server自定义字符串操作函数

1.计算一个字符串在其他字符串中出现的次数

--计算inner_string在outer_string中出现的次数
if object_id('COUNT_CHARACTERS','p') is not null
drop proc COUNT_CHARACTERS
go
 
CREATE PROC COUNT_CHARACTERS
           @outer_string   VARCHAR(400),
           @inner_string VARCHAR(50)
AS
  DECLARE  @i INT,
                 @n INT
  
  SET @n = 0
  
  WHILE CHARINDEX(@inner_string,@outer_string) <> 0
    BEGIN
      SELECT @i = CHARINDEX(@inner_string,@outer_string)
      
      SET @outer_string = SUBSTRING(@outer_string,(@i + LEN(@inner_string)),
                                  (LEN(@outer_string) - (@i + LEN(@inner_string)-1 )))
           
      SET @n = @n + 1
    END
    
    select @n
GO

 

2.得到字符串中起始点到终点之间的字符串，包括终点位置 

--得到字符串中起始点到终点之间的字符串，包括终点位置
if object_id('GET_STRING_BETWEEN_TWO_CHARACTERS','p') is not null
drop proc GET_STRING_BETWEEN_TWO_CHARACTERS
go

CREATE PROC GET_STRING_BETWEEN_TWO_CHARACTERS
           @string VARCHAR(2000),
           @start  INT,
           @end    INT
AS
  DECLARE  @output VARCHAR(2000)
  
  IF @start > LEN(@string)
    BEGIN
      SELECT '起始点位置大于字符串长度'
      
      RETURN
    END
  ELSE
    IF @start >= @end
      BEGIN
        SELECT '结束点位置小于起始点'
        
        RETURN
      END
  
  SELECT @output = SUBSTRING(@string,@start,(@end - @start +1))
  
  select @output
GO

 

3.字符串部分反向

字符串全部反向用函数REVERSE

 

if object_id('REVERSE_ORDER','FN') is not null
drop function REVERSE_ORDER
go

CREATE FUNCTION REVERSE_ORDER
               (@string VARCHAR(8000),
                @start  INT,
                @end   INT)
RETURNS VARCHAR(8000)
AS
  BEGIN
    IF @start > @end
      BEGIN
        RETURN '结束点位置小于起始点'
      END
    
    IF @start > LEN(@string)
        OR @end > LEN(@string)
      BEGIN
        RETURN '请在字符串的长度内取值'
      END
    
  declare @OutPut varchar(8000)

  if @start > 1   
  begin
    select @OutPut = SUBSTRING(@string,1,@start - 1)
    select @OutPut =  @OutPut + REVERSE(SUBSTRING(@string,@start,(@end - @start + 1)))  
  end
  else begin
    select @OutPut =  REVERSE(SUBSTRING(@string,@start,(@end - @start + 1)))  
  end
 
  if @end < len(@string)
  begin
     select @OutPut = @OutPut + SUBSTRING(@string,@end+1,(LEN(@string) - @end))
  end

    RETURN  @OutPut
  END