SQL Server Implementation Of MySQL Function SUBSTRING_INDEX(str,delim,count)
SQL Server No Comments »SELECT SubString_Index(’Bulgaria.Sofia.Mladost’ , ‘ . ‘ , 2)
Return ‘Mladost’
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
Create FUNCTION SubString_Index (@Param varchar(8000),@Separator varchar(1)=’.', @Count int)
RETURNS varchar(1024)
– Description - Return a substring from a string before the specified number of occurrences of the delimiter
– Example - select AdventureWorks.dbo.substring_index(’11,22,33,44,55′,’,',2)
– Created by Ilia Dobrev
– Version 1.01
as
begin
declare @PatIndex int
declare @CountIndex int
set @CountIndex = 0
if(SubString(@Param,len(@Param),1)<>@Separator) set @Param =@Param + @Separator
while len(@Param) > 0
begin
set @CountIndex = @CountIndex +1
set @PatIndex = patindex(’%’ + @Separator + ‘%’,@Param)
if @PatIndex <> 0
begin
if(@CountIndex = @Count )return left(@Param, @PatIndex - 1)
set @Param = (select right(@Param, len(@Param) - @PatIndex))
end
else
begin
set @Param = ”
return ”
end
end
return ”
end
Recent Comments