6 Jan 2014
I’m porting some code from MySQL to Microsoft MSSQL, and unfortunately MSSQL doesn’t have a SUBSTRING_INDEX function. Here’s a version I quickly implemented, called my_substring_index.
Limitations:
- only accepts strings up to 255 chars long
- only works with varchars - we speak ASCII here…
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION [dbo].[my_substring_index]
(@str VARCHAR (255), @delim VARCHAR (1), @count INT)
RETURNS VARCHAR (255)
AS
BEGIN
DECLARE @result AS VARCHAR (255), @posn AS INT, @loop AS INT,
@found AS INT, @reversed AS INT;
SET @loop = 0;
SET @posn = -1;
SET @found = 0;
SET @reversed = 0;
IF @count < 0
BEGIN
SET @reversed = 1;
SET @count = @count * -1;
SET @str = REVERSE(@str);
END
WHILE @loop < @count
BEGIN
SET @posn = charindex(@delim, @str, @posn + 1);
IF @posn > 0
SET @found = 1;
ELSE
IF @found = 1 AND @reversed = 0
RETURN @str; -- ie mimic mysql behaviour
ELSE
IF @found = 1 AND @reversed = 1
RETURN REVERSE(@str); -- ie mimic mysql behaviour
SET @loop = @loop + 1;
END
IF @posn >= 0 AND @reversed = 0
RETURN SUBSTRING(@str, 0, @posn);
ELSE
IF @posn >= 0 AND @reversed = 1
RETURN REVERSE(SUBSTRING(@str, 0, @posn));
RETURN '';
END
SELECT dbo.my_substring_index('www.mysql.com', '.', -1)
--------------------------------------------------------
com
(1 row(s) affected)