我正在尝试将查询从 MySQL 移植到 SQL SERVER 2012.
我如何为 MySQL 的 substring_index() 编写等价物?
I am trying to port a query from MySQL to SQL SERVER 2012.
How do i write an equivalent for MySQL's substring_index()?
MySQL SUBSTRING_INDEX() 返回给定字符串之前的子字符串指定的分隔符出现次数.
MySQL SUBSTRING_INDEX() returns the substring from the given string before a specified number of occurrences of a delimiter.
SUBSTRING_INDEX(str, delim, count)
SUBSTRING_INDEX(str, delim, count)
SELECT SUBSTRING_INDEX('www.somewebsite.com','.',2);
输出:'www.somewebsite'
试试这个基于 T-SQL 和 XQuery 的解决方案((root/row)[position() <= sql:variable("@count")]
):
Try this solution based on T-SQL and 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
*/
这篇关于SQL Server 相当于 MySQL 中的 substring_index 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!