MySQL SUBSTRING_INDEX function on Microsoft MSSQL

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)

comments powered by Disqus

  « Previous: Next: »