SQL Server 相当于 ORACLE INSTR [英] SQL Server Equivalent to ORACLE INSTR
问题描述
我想知道在 SQL Server 中是否有与 Oracle INSTR
函数等效的函数?
我知道有 CHARINDEX
和 PATINDEX
,但是使用 Oracle 版本我还可以指定我要查找的字符的第 N 个外观.
I wanted to know if in SQL Server there is an equivalent to the Oracle INSTR
function?
I know that there is CHARINDEX
and PATINDEX
, but with the Oracle version I can also specify the Nth appearance of the character(s) I am looking for.
Oracle INSTR
:
instr( string1, string2 [, start_position [, **nth_appearance** ] ] )
CHARINDEX
几乎让我到达那里,但我想让它从字符串中字符的 nth_appearance
开始.>
The CHARINDEX
almost gets me there, but I wanted to have it start at the nth_appearance
of the character in the string.
推荐答案
您发现 nth_appearance
在 SQL Server 中不存在.
You were spot on that nth_appearance
does not exist in SQL Server.
无耻地复制一个函数(与 SQL Server 中带有 4 个参数的 Oracle 的 INSTR 等效)为您的问题创建(请注意,@Occurs 的使用方式与 Oracle 中的不同 - 您不能指定第三次出现",但是出现 3 次"):
Shamelessly copying a function (Equivalent of Oracle's INSTR with 4 parameters in SQL Server) created for your problem (please note that @Occurs is not used the same way as in Oracle - you can't specify "3rd appearance", but "occurs 3 times"):
CREATE FUNCTION udf_Instr
(@str1 varchar(8000), @str2 varchar(1000), @start int, @Occurs int)
RETURNS int
AS
BEGIN
DECLARE @Found int, @LastPosition int
SET @Found = 0
SET @LastPosition = @start - 1
WHILE (@Found < @Occurs)
BEGIN
IF (CHARINDEX(@str1, @str2, @LastPosition + 1) = 0)
BREAK
ELSE
BEGIN
SET @LastPosition = CHARINDEX(@str1, @str2, @LastPosition + 1)
SET @Found = @Found + 1
END
END
RETURN @LastPosition
END
GO
SELECT dbo.udf_Instr('x','axbxcxdx',1,4)
GO
DROP FUNCTION udf_Instr
GO
这篇关于SQL Server 相当于 ORACLE INSTR的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!