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