Looking for (substring_index of MySQL's function)

Could you please let me know what is the substitute function of (substring_index of MySQL) in SQL Server?
Note : I know it I can do using CharIndex. But I want this type of feature.Please let me know.
Thanks!
Abhi


mysql> select substring_index('abhi,abcd,12,15',',',1);
+------------------------------------------+
| substring_index('abhi,abcd,12,15',',',1) |
+------------------------------------------+
| abhi |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index('abhi,abcd,12,15',',',2);
+------------------------------------------+
| substring_index('abhi,abcd,12,15',',',2) |
+------------------------------------------+
| abhi,abcd |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index('abhi,abcd,12,15',',',3);
+------------------------------------------+
| substring_index('abhi,abcd,12,15',',',3) |
+------------------------------------------+
| abhi,abcd,12 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index('abhi,abcd,12,15',',',-1);
+-------------------------------------------+
| substring_index('abhi,abcd,12,15',',',-1) |
+-------------------------------------------+
| 15 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index('abhi,abcd,12,15',',',-2);
+-------------------------------------------+
| substring_index('abhi,abcd,12,15',',',-2) |
+-------------------------------------------+
| 12,15 |
+-------------------------------------------+
1 row in set (0.00 sec)

substring_index is a very useful function, but there is no MSSQL equivalent. You would have to write your own user defined function if you really needed this.

For anyone who doesnt know, the func takes str, delim and count, and returns..

if count > 0 --> the left part of the string before 'count' occurences of delim are found
if count < 0 --> the right part of the string after abs(count) occurences of delim are found

Thnaks for your info.Could you pls tell me how to write a function .or where
where should I start writing function in sql server?
Regards,
Abhi

If you need the result as a string a scalar function would do.


CREATE FUNCTION substring_index(@string varchar(8000), @delimiter char(1), @count int)
RETURNS varchar(8000)
AS
BEGIN

DECLARE @result varchar(8000)

/*
in the function body, find the delimiter according to
the @count (you can use CHARINDEX) then return the
substring you need.
*/

RETURN @result
END
Bambola.

BTW, to call the function you need to specify the owner. in this case it could be
dbo.substring_index(.....)

Bambola.

thanks! your your help.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create FUNCTION [dbo].fnSubstring_Index
(
@BaseString varchar(255),
@caracter varchar(255),
@pos tinyint
) RETURNS varchar(255)

AS
/* ****************************************************
Description:
EQuivalent a mysql substring_index---- ---- -----------
Create by Omar Rodriguez Tineo, From Santiago, Dominican Republic.
Please dont remove this if you like the function
**************************************************** */
BEGIN

/*
DECLARE @pos INT
Declare @BaseString varchar(255)
Declare @caracter varchar(255)
*/
Declare @indice tinyint
Declare @pos2 tinyint
Declare @result varchar(255)

set @pos2= 1
set @indice = 0
--set @BaseString='hola mudo sato bueno cinco seis siete'
--set @pos = 2
--set @caracter= ' '


while @indice < @pos
begin
begin


set @pos2 = CHARINDEX(@caracter,@BaseString,@pos2+1)
-- print @pos2

set @indice = @indice +1

end


if @indice = @pos
begin
set @result= left(@BaseString,@pos2)
--print @result
break
end
else
continue

end




RETURN @result
END