SQL Server equivalent of substring_index function in MySQL

Try this solution based on T-SQL and XQuery((root/row)[position() <= sql:variable("@count")]):

T-SQL Scalar function:

CREATE FUNCTION dbo.SUBSTRING_INDEX(    @str NVARCHAR(4000),    @delim NVARCHAR(1),    @count INT)RETURNS NVARCHAR(4000)WITH SCHEMABINDINGBEGIN    DECLARE @XmlSourceString XML;    SET @XmlSourceString = (SELECT N'<root><row>' + REPLACE( (SELECT @str AS '*' FOR XML PATH('')) , @delim, N'</row><row>' ) + N'</row></root>');    RETURN STUFF    (        ((            SELECT  @delim + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(4000)') AS '*'            FROM    @XmlSourceString.nodes(N'(root/row)[position() <= sql:variable("@count")]') x(XmlCol)            FOR XML PATH(N''), TYPE        ).value(N'.', N'NVARCHAR(4000)')),         1, 1, N''    );ENDGOSELECT dbo.SUBSTRING_INDEX(N'www.somewebsite.com', N'.', 2) AS Result;

Output:

/*Result---------------www.somewebsite*/

or

TSQL Inline Table-Valued Function:

CREATE FUNCTION dbo.SUBSTRING_INDEX(    @str NVARCHAR(4000),    @delim NVARCHAR(1),    @count INT)RETURNS TABLEAS RETURN    WITH Base    AS     (        SELECT XmlSourceString = CONVERT(XML, (SELECT N'<root><row>' + REPLACE( (SELECT @str AS '*' FOR XML PATH('')) , @delim, N'</row><row>' ) + N'</row></root>'))    )       SELECT STUFF    (        ((            SELECT  @delim + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(4000)') AS '*'            FROM    Base b             CROSS APPLY b.XmlSourceString.nodes(N'(root/row)[position() <= sql:variable("@count")]') x(XmlCol)            FOR XML PATH(N''), TYPE        ).value(N'.', N'NVARCHAR(4000)')),         1, 1, N''    ) AS Result;GOSELECT  *FROM    (    SELECT N'www.somewebsite.com' UNION ALL     SELECT N'www.yahoo.com' UNION ALL     SELECT N'www.outlook.com') a(Value)CROSS APPLY dbo.SUBSTRING_INDEX(a.Value, N'.', 2) b;

Output:

/*Value               Result------------------- ---------------www.somewebsite.com www.somewebsitewww.yahoo.com       www.yahoowww.outlook.com     www.outlook*/

I needed this recently, so I wrote the following stored function. At the end are a bunch of tests to make sure it operates exactly as the MySql function does (the expected results were copied from MySql after running the same tests there):

-- Function to reproduce the useful functionality of SUBSTRING_INDEX from MySqlCREATE FUNCTION dbo.SUBSTRING_INDEX(@InString  NVARCHAR(Max),                                    @Delimiter NVARCHAR(Max),                                    @Count     INT)RETURNS NVARCHAR(200)ASBEGIN    DECLARE @Pos INT;    DECLARE @DelimiterOffsets TABLE    (         i      INT IDENTITY(1, 1) NOT NULL,         offset INT NOT NULL    );    -- If @Count is zero, we return '' as per spec    IF @Count = 0    BEGIN        RETURN '';    END;    DECLARE @OrigLength      INT = LEN(@InString);    DECLARE @DelimiterLength INT = LEN(@Delimiter);    -- Prime the pump.    SET @Pos = Charindex(@Delimiter, @InString, 1);    -- If the delimiter does not exist in @InString, return the whole string    IF @Pos = 0    BEGIN        RETURN @InString;    END;    -- Put all delimiter offsets into @DelimiterOffsets, they get numbered automatically.    DECLARE @CurrentOffset INT = 0;    WHILE @Pos > 0    BEGIN        SET @CurrentOffset = @Pos;        INSERT INTO @DelimiterOffsets                    (offset)             VALUES (@CurrentOffset);        SET @Pos = Charindex(@Delimiter, @InString, @CurrentOffset + @DelimiterLength);    END;    -- This number is guaranteed to be > 0.    DECLARE @DelimitersFound INT = (SELECT Count(*) FROM @DelimiterOffsets);    -- If they requested more delimiters than were found, return the whole string, as per spec.    IF Abs(@Count) > @DelimitersFound    BEGIN        RETURN @InString;    END;    DECLARE @StartSubstring INT = 0;    DECLARE @EndSubstring   INT = @OrigLength;    -- OK, now return the part they requested    IF @Count > 0    BEGIN        SET @EndSubstring = (SELECT offset                                FROM @DelimiterOffsets                               WHERE i = @Count);    END    ELSE    BEGIN        SET @StartSubstring = (SELECT offset + @DelimiterLength                                  FROM @DelimiterOffsets                                 WHERE i = (@DelimitersFound + @Count + 1));    END;    RETURN Substring(@InString, @StartSubstring, @EndSubstring);END; Go GRANT EXECUTE ON [dbo].SUBSTRING_INDEX TO PUBLIC;-- TestsDECLARE @TestResults TABLE (i int, answer nVarChar(MAX), expected nVarChar(MAX));insert into @TestResultsselect * from  (    (SELECT  1 as i, [dbo].SUBSTRING_INDEX(N'www.somewebsite.com', N'.', 2)    as r, 'www.somewebsite'     as e) UNION    (SELECT  2 as i, [dbo].SUBSTRING_INDEX(N'www.yahoo.com', N'.', 2)          as r, 'www.yahoo'           as e) UNION    (SELECT  3 as i, [dbo].SUBSTRING_INDEX(N'www.outlook.com', N'.', 2)        as r, 'www.outlook'         as e) UNION    (SELECT  4 as i, [dbo].SUBSTRING_INDEX(N'www.somewebsite.com', N'.', -2)   as r, 'somewebsite.com'     as e) UNION    (SELECT  5 as i, [dbo].SUBSTRING_INDEX(N'www.yahoo.com', N'.', -2)         as r, 'yahoo.com'           as e) UNION    (SELECT  6 as i, [dbo].SUBSTRING_INDEX(N'www.outlook.com', N'.', -2)       as r, 'outlook.com'         as e) UNION    (select  7 as i, [dbo].SUBSTRING_INDEX('hi.you.com','.',2)                 as r, 'hi.you'              as e) UNION    (select  8 as i, [dbo].SUBSTRING_INDEX('hi.you.com','.',-1)                as r, 'com'                 as e) UNION    (select  9 as i, [dbo].SUBSTRING_INDEX(N'prueba','ue',1)                   as r, 'pr'                  as e) UNION    (select 10 as i, [dbo].SUBSTRING_INDEX(N'prueba','ue',-1)                  as r, 'ba'                  as e) UNION    (select 11 as i, [dbo].SUBSTRING_INDEX(N'prueba','ue',0)                   as r, ''                    as e) UNION    (SELECT 12 as i, [dbo].SUBSTRING_INDEX(N'wwwxxxoutlookxxxcom', N'xxx', 2)  as r, 'wwwxxxoutlook'       as e) UNION    (SELECT 13 as i, [dbo].SUBSTRING_INDEX(N'wwwxxxoutlookxxxcom', N'xxx', -2) as r, 'outlookxxxcom'       as e) UNION    (SELECT 14 as i, [dbo].SUBSTRING_INDEX(N'wwwxxxoutlookxxxcom', N'xxx', 5)  as r, 'wwwxxxoutlookxxxcom' as e) UNION    (SELECT 15 as i, [dbo].SUBSTRING_INDEX(N'wwwxxxoutlookxxxcom', N'xxx', -5) as r, 'wwwxxxoutlookxxxcom' as e)) as results;select tr.i,       tr.answer,       tr.expected,       CASE WHEN tr.answer = tr.expected THEN 'Test Succeeded' ELSE 'Test Failed' END testState  from @TestResults tr order by i;

Here's a version inspired by Bogdan Sahlean's answer using SQL Server's XML functionality to do the parsing and combining:

CREATE FUNCTION dbo.SUBSTRING_INDEX(@InString  NVARCHAR(Max),                                    @Delimiter NVARCHAR(Max),                                    @Count     INT)RETURNS NVARCHAR(200)ASBEGIN    -- If @Count is zero, we return '' as per spec    IF @Count = 0    BEGIN        RETURN '';    END;    -- First we let the XML parser break up the string by @Delimiter.    -- Each parsed value will be <piece>[text]</piece>.    DECLARE @XmlSourceString XML = (select N'<piece>' + REPLACE( (SELECT @InString AS '*' FOR XML PATH('')) , @Delimiter, N'</piece><piece>' ) + N'</piece>');    -- This will contain the final requested string.    DECLARE @Results nVarChar(MAX);    ;WITH Pieces(RowNumber, Piece) as    (        -- Take each node in @XmlSourceString, and return it with row numbers        -- which will identify each piece and give us a handle to change the        -- order, depending on the direction of search.        SELECT  row_number() over(order by x.XmlCol) as RowNumber,                @Delimiter + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(MAX)') AS '*'          FROM  @XmlSourceString.nodes(N'(piece)') x(XmlCol)    ), orderedPieces(RowNumber, Piece) as    (        -- Order the pieces normally or reversed depending on whether they want        -- the first @Count pieces or the last @Count pieces.        select TOP (ABS(@Count))                RowNumber,                Piece          from Pieces         ORDER BY CASE WHEN @Count < 0 THEN RowNumber END DESC ,                  CASE WHEN @Count > 0 THEN RowNumber END ASC    ), combinedPieces(result) as    (        -- Now combine the pieces back together, ordering them by        -- the original order.  There will always        -- be an extra @Delimiter on the front of the string.        select CAST(Piece AS VARCHAR(100))          from OrderedPieces         order by RowNumber           FOR XML PATH(N'')    )    -- Finally, strip off the extra delimiter using STUFF and store the string in @Results.    select @Results = STUFF(result, 1, LEN(@Delimiter), '') from combinedPieces;    return @Results;END;

Running the tests produces this:

i  answer              expected             testState1  www.somewebsite     www.somewebsite      Test Succeeded2  www.yahoo           www.yahoo            Test Succeeded3  www.outlook         www.outlook          Test Succeeded4  somewebsite.com     somewebsite.com      Test Succeeded5  yahoo.com           yahoo.com            Test Succeeded6  outlook.com         outlook.com          Test Succeeded7  hi.you              hi.you               Test Succeeded8  com                 com                  Test Succeeded9  pr                  pr                   Test Succeeded10 ba                  ba                   Test Succeeded11                                          Test Succeeded12 wwwxxxoutlook       wwwxxxoutlook        Test Succeeded13 outlookxxxcom       outlookxxxcom        Test Succeeded14 wwwxxxoutlookxxxcom wwwxxxoutlookxxxcom  Test Succeeded15 wwwxxxoutlookxxxcom wwwxxxoutlookxxxcom  Test Succeeded

    CREATE FUNCTION FN_SUBSTRING_INDEX    (       @TEXTO NVARCHAR(200),      @SUBSTRING_INDEX NVARCHAR(10),       @DESPLAZAMIENTO INT    )    RETURNS NVARCHAR(200)    AS    BEGIN        DECLARE @indiceSubstring INT        DECLARE @RESULTADO NVARCHAR(200)        SELECT @indiceSubstring = CHARINDEX(@SUBSTRING_INDEX,@TEXTO)        IF @DESPLAZAMIENTO > 0        BEGIN            SELECT @RESULTADO=SUBSTRING(@TEXTO,@indiceSubstring+@DESPLAZAMIENTO+1,LEN(@TEXTO))        END         ELSE        BEGIN            SELECT @RESULTADO=SUBSTRING(@TEXTO,0,@indiceSubstring-@DESPLAZAMIENTO-1)        END     RETURN @RESULTADO    END    GO

to rightselect dbo.FN_SUBSTRING_INDEX(N'prueba','ue',1);

to leftselect dbo.FN_SUBSTRING_INDEX(N'prueba','ue',-1);