【Sqlserver】【Function】实现mysql substring_index

sqlServer实现Mysql的substring_index函数

• step1 基本实现

IF OBJECT_ID('dbo.SubstringIndex') IS NOT NULL
    DROP  FUNCTION dbo.SubstringIndex

GO

CREATE FUNCTION dbo.SubstringIndex(
    @SourceString varchar(8000),
    @delim char(1),
    @idx int
)
RETURNS TABLE WITH SCHEMABINDING
RETURN
WITH 
E(n) AS(
    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
    SELECT a.n FROM E a, E b
),
E4(n) AS(
    SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
    SELECT TOP(LEN(@SourceString)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
    FROM E4
),
ctePosition(n) AS(
    SELECT TOP( @idx) n
    FROM cteTally
    WHERE SUBSTRING(@SourceString, n, 1) = @delim
)
SELECT LEFT( @SourceString, MAX(n) - 1) String
FROM ctePosition;       
go

调用：

declare @SourceStr varchar(8000) = 'www.mytestpage.info',
        @delim char(1) = '.',
        @idx int = 2;

select string from dbo.SubstringIndex(@SourceStr,@delim,@idx); --www.mytestpage

• step2 完全实现等价调用效果

IF OBJECT_ID('dbo.substring_index') IS NOT NULL
    DROP  FUNCTION dbo.Substring_Index

GO

CREATE FUNCTION dbo.Substring_Index(
    @SourceString varchar(8000),
    @delim char(1),
    @idx int
)
RETURNS varchar(8000) 
AS
begin
declare @return varchar(8000)
select @return=(select top 1 string from dbo.SubstringIndex(@SourceString,@delim,@idx))
return(@return) 
end
go

最终等价实现效果：

declare @str varchar(19)
set @str='test_big_string';

select dbo.substring_index(@str,'_',2) -- test_big

在Mysql的SQL中将substring_index替换为dbo.substring_index即可。

参考资料：http://www.sqlservercentral.com/Forums/Topic1445667-392-1.aspx

补充：函数在字符串为空和参数为负数、空值等情况与mysql的结果不一致，修改如下：

IF OBJECT_ID('dbo.substring_index') IS NOT NULL
    DROP  FUNCTION dbo.Substring_Index
GO

CREATE FUNCTION dbo.Substring_Index(
    @SourceString varchar(8000),
    @delim char(1),
    @idx int
)
RETURNS varchar(8000) 
AS
begin
declare @return varchar(8000)

select @return=case  
/*针对空值进行处理*/ 
when @SourceString is null or @idx is null then null 
/*字符串中无分割符的情况*/
when len(@SourceString)=len(replace(@SourceString,@delim,'')) then @SourceString 
/*有分隔符，但是分隔符定位数>字符串中的分隔符计数*/
when abs(@idx)>(len(@SourceString)-len(replace(@SourceString,@delim,''))) then @SourceString 
/*负值处理*/
when @idx<0 then replace(@SourceString,(select top 1 string from dbo.SubstringIndex(@SourceString,@delim,len(@SourceString)+1-len(replace(@SourceString,@delim,''))+@idx))+@delim,'')  
/*正值处理*/
  else (select top 1 string from dbo.SubstringIndex(@SourceString,@delim,@idx)) end
return(@return) 
end
go