mysql - SQL Server 等效于 MySQL 中的 substring_index 函数

Question

我正在尝试将查询从 MySQL 移植到 SQL SERVER 2012。

我如何为 MySQL 的 substring_index() 编写等价物？

MySQL SUBSTRING_INDEX()返回给定字符串中出现指定次数的定界符之前的子字符串。

SUBSTRING_INDEX(str, delim, count)

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

输出:'www.somewebsite'

Answer 1

试试这个基于 T-SQL 和 XQuery 的解决方案((root/row)[position() <= sql:variable("@count")]):

T-SQL 标量函数:

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() <= sql:variable("@count")]') x(XmlCol)
            FOR XML PATH(N''), TYPE
        ).value(N'.', N'NVARCHAR(4000)')), 
        1, 1, N''
    );
END
GO

SELECT dbo.SUBSTRING_INDEX(N'www.somewebsite.com', N'.', 2) AS Result;

输出:

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

或

TSQL 内联表值函数:

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() <= sql:variable("@count")]') x(XmlCol)
            FOR XML PATH(N''), TYPE
        ).value(N'.', N'NVARCHAR(4000)')), 
        1, 1, N''
    ) AS Result;
GO

SELECT  *
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;

输出:

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