SQL Server相当于MySQL中的substring_index函数

尝试基于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 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;

输出：

要么

TSQL内联表值函数：

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;

输出：