SQL Server相当于MySQL中的substring_index函数
尝试基于T-sql和XQuery（(root/row)[position() <= sql:variable(“@count”)]）的以下解决方案：
codelayui.code
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;
输出：
codelayui.code
/*
Result
---------------
www.somewebsite
*/
要么
codelayui.code
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;
输出：
codelayui.code
/*
Value Result
------------------- ---------------
www.somewebsite.com www.somewebsite
www.yahoo.com www.yahoo
www.outlook.com www.outlook
*/
