[Sql Server][原创] - 字符串分隔函数

-- 返回字符串以分隔符分隔的第n（1/2/3……）个字符换

USE [EPICOR10]
GO
/****** Object:  UserDefinedFunction [dbo].[AH_Entry]    Script Date: 2017-12-23 16:30:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
    改自：Ice.Entry(@idx int, @char_expr nvarchar(max), @delim nchar(1))

    select ice.Entry(1,'http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg','Tiger/')
    select dbo.AH_Entry(2,'http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg','Tiger/')

    select SubString(dbo.AH_Entry(dbo.AH_Num_Entries('http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg','Tiger/'),'http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg','Tiger/'),0,CharIndex('/',dbo.AH_Entry(dbo.AH_Num_Entries('http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg','Tiger/'),'http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg','Tiger/')))
*/

ALTER function [dbo].[AH_Entry](@idx int, @char_expr nvarchar(max), @delim nvarchar(100))
returns nvarchar(max)
as
begin
  declare @begin int, @end int, @count int
  declare @str nvarchar(max)
  set @count = 1
  set @begin = 1
  set @end = charindex(@delim, @char_expr)
  if @idx <= 0
    return (NULL)
  if (@end = 0) and @idx > 1
    return (NULL)
  if (@end = 0) and @idx = 1
    return @char_expr
  set @str = @char_expr
  while charindex(@delim, @str) > 0 and @count < @idx
  begin
    set @begin = charindex(@delim, @str) + len(@delim)
    set @str = substring(@str, @begin, len(@str) - @begin + 1)
    set @count = @count + 1
  end
  if @count < @idx
    return (NULL)
  if charindex(@delim, @str) > 0
    set @str = substring(@str, 1, charindex(@delim, @str) - 1)
  else
    set @str = substring(@str, 1, len(@str))
  return (@str)
end

-- 返回字符串以分隔符分隔的字符数

USE [EPICOR10]
GO
/****** Object:  UserDefinedFunction [dbo].[AH_Num_Entries]    Script Date: 2017-12-23 16:35:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
    改自：Ice.num_entries(@char_expr nvarchar(max), @delim nchar(1))

    select ice.num_entries('http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg','Tiger/')
    select dbo.AH_Num_Entries('http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg','Tiger/')

    select dbo.AH_Num_Entries('http://192.168.0.251/PackInfo/Tiger/FM/TG17/Tiger/PC/Chest Strap/A1.jpg','Tiger/')
*/


ALTER function [dbo].[AH_Num_Entries](@char_expr nvarchar(max), @delim nvarchar(100))
returns int
as
begin
  declare @begin int, @count int
  declare @str nvarchar(max)
  set @count = 1
  set @begin = 1
  if len(@char_expr) <= 0
    return (0)
  set @str = @char_expr
  while charindex(@delim, @str) > 0
  begin
    set @begin = charindex(@delim, @str) + len(@delim)
    set @str = substring(@str, @begin, len(@str) - @begin + 1)
    set @count = @count + 1
  end
  return (@count)
end