SQL Server Performance Forum – Threads Archive

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.

---

Abhi also refer to books online for basic information and syntax help as a first hand information. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

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

---

Omar,
Welcome to the forums!!.
Even this thread is 8 years old, we appreciate your collaboration.

---

]]>