SQL Server equivalent of substring_index function in MySQL

Question

I am trying to port a query from MySQL to SQL SERVER 2012.

How do i write an equivalent for MySQL's substring_index()?

MySQL SUBSTRING_INDEX() returns the substring from the given string before a specified number of occurrences of a delimiter.

SUBSTRING_INDEX(str, delim, count)

SELECT SUBSTRING_INDEX('www.somewebsite.com','.',2);

Output: 'www.somewebsite'

Answer 1

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 MySql
CREATE FUNCTION dbo.SUBSTRING_INDEX(@InString  NVARCHAR(Max),
                                    @Delimiter NVARCHAR(Max),
                                    @Count     INT)
RETURNS NVARCHAR(200)
AS
BEGIN
    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;

-- Tests
DECLARE @TestResults TABLE (i int, answer nVarChar(MAX), expected nVarChar(MAX));

insert into @TestResults
select * 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)
AS
BEGIN
    -- 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             testState
1  www.somewebsite     www.somewebsite      Test Succeeded
2  www.yahoo           www.yahoo            Test Succeeded
3  www.outlook         www.outlook          Test Succeeded
4  somewebsite.com     somewebsite.com      Test Succeeded
5  yahoo.com           yahoo.com            Test Succeeded
6  outlook.com         outlook.com          Test Succeeded
7  hi.you              hi.you               Test Succeeded
8  com                 com                  Test Succeeded
9  pr                  pr                   Test Succeeded
10 ba                  ba                   Test Succeeded
11                                          Test Succeeded
12 wwwxxxoutlook       wwwxxxoutlook        Test Succeeded
13 outlookxxxcom       outlookxxxcom        Test Succeeded
14 wwwxxxoutlookxxxcom wwwxxxoutlookxxxcom  Test Succeeded
15 wwwxxxoutlookxxxcom wwwxxxoutlookxxxcom  Test Succeeded