How to Replicate MySQL's SUBSTRING_INDEX() Function in SQL Server?

SQL Server Equivalent of MySQL's SUBSTRING_INDEX() Function

The MySQL SUBSTRING_INDEX() function extracts a substring from a given string based on the number of occurrences of a specified delimiter. To replicate this functionality in SQL Server, several approaches can be employed.

One method involves leveraging T-SQL and XQuery to create a scalar function:

CREATE FUNCTION dbo.SUBSTRING_INDEX
(
    @str NVARCHAR(4000),
    @delim NVARCHAR(1),
    @count INT
)
RETURNS NVARCHAR(4000)
WITH SCHEMABINDING
BEGIN
    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() <p>Another approach utilizes an inline table-valued function in TSQL:</p><pre class="brush:php;toolbar:false">CREATE FUNCTION dbo.SUBSTRING_INDEX
(
    @str NVARCHAR(4000),
    @delim NVARCHAR(1),
    @count INT
)
RETURNS TABLE
AS 
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() <p>Both these solutions provide efficient alternatives to MySQL's SUBSTRING_INDEX() function, enabling seamless porting of queries between the two database systems.</p>

The above is the detailed content of How to Replicate MySQL's SUBSTRING_INDEX() Function in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!