我正在尝试将查询从MySQL移植到SQL SERVER2012。
如何为MySQL的substring_index()编写等效项?
MySQL SUBSTRING_INDEX()在指定次数的定界符之前返回给定字符串的子字符串。
SUBSTRING_INDEX(str,delim,count)
SELECT SUBSTRING_INDEX('www.somewebsite.com','.',2);
输出: 'www.somewebsite'
尝试基于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
*/
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句