使用 T-SQL 查找子字符串最后一次出现的索引

有些其他答案返回实际字符串，而我更需要知道实际索引int。那些答案似乎使事情过于复杂化了。受其他答案的启发，我做了以下操作...

首先，我创建了一个函数：

CREATE FUNCTION [dbo].[LastIndexOf] (@stringToFind varchar(max), @stringToSearch varchar(max))
RETURNS INT
AS
BEGIN
    RETURN (LEN(@stringToSearch) - CHARINDEX(@stringToFind,REVERSE(@stringToSearch))) + 1
END
GO

然后，在您的查询中，您只需执行以下操作：

declare @stringToSearch varchar(max) = 'SomeText: SomeMoreText: SomeLastText'

select dbo.LastIndexOf(':', @stringToSearch)

上面的代码应该返回23（即“:”的最后一个索引位置）。
希望这能让某些人更容易理解！

我知道这是一个几年前的问题，但是...

在 Access 2010 中，你可以使用 InStrRev() 来完成此操作。希望这可以帮到你。

如果您想获取一个由单词组成的字符串中最后一个空格的索引，可以使用以下表达式RIGHT(name, (CHARINDEX(' ',REVERSE(name),0))来返回该字符串中的最后一个单词。如果您想解析出包含首字母缩写的全名中的姓氏，那么这将非常有用。请注意保留HTML标签。

将字符串和子字符串都反转，然后搜索第一次出现的位置。

这段代码即使子字符串包含多个字符也能正常工作。

DECLARE @FilePath VARCHAR(100) = 'My_sub_Super_sub_Long_sub_String_sub_With_sub_Long_sub_Words'
DECLARE @FindSubstring VARCHAR(5) = '_sub_'

-- Shows text before last substing
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) - LEN(@FindSubstring) + 1) AS Before
-- Shows text after last substing
SELECT RIGHT(@FilePath, CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) -1) AS After
-- Shows the position of the last substing
SELECT LEN(@FilePath) - CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) AS LastOccuredAt

@indexOf = <在字符串中搜索的任何字符>

@LastIndexOf = LEN([MyField]) - CHARINDEX(@indexOf, REVERSE([MyField]))

尚未经过测试，可能由于零索引而出现偏差，但在从@indexOf字符截断到您字符串末尾的SUBSTRING函数中有效。

SUBSTRING([MyField], 0, @LastIndexOf)

我知道这样做会很低效，但你考虑过将text字段转换为varchar，以便使用你找到的网站提供的解决方案吗？我知道这个解决方案会带来问题，因为如果text字段的长度超过了你的varchar的长度，你可能会截断记录（更不用说它的性能不是很好）。

由于你的数据在一个text字段中（并且你正在使用SQL Server 2000），你的选择有限。

我需要找到文件夹路径中倒数第n个反斜杠的位置。这是我的解决方案。

/*
https://dev59.com/1HNA5IYBdhLWcg3wSrqa#30904809
DROP FUNCTION dbo.GetLastIndexOf
*/
CREATE FUNCTION dbo.GetLastIndexOf
(
  @expressionToFind         VARCHAR(MAX)
  ,@expressionToSearch      VARCHAR(8000)
  ,@Occurrence              INT =  1        -- Find the nth last 
)
RETURNS INT
AS
BEGIN

    SELECT  @expressionToSearch = REVERSE(@expressionToSearch)

    DECLARE @LastIndexOf        INT = 0
            ,@IndexOfPartial    INT = -1
            ,@OriginalLength    INT = LEN(@expressionToSearch)
            ,@Iteration         INT = 0

    WHILE (1 = 1)   -- Poor man's do-while
    BEGIN
        SELECT @IndexOfPartial  = CHARINDEX(@expressionToFind, @expressionToSearch)

        IF (@IndexOfPartial = 0) 
        BEGIN
            IF (@Iteration = 0) -- Need to compensate for dropping out early
            BEGIN
                SELECT @LastIndexOf = @OriginalLength  + 1
            END
            BREAK;
        END

        IF (@Occurrence > 0)
        BEGIN
            SELECT @expressionToSearch = SUBSTRING(@expressionToSearch, @IndexOfPartial + 1, LEN(@expressionToSearch) - @IndexOfPartial - 1)
        END

        SELECT  @LastIndexOf = @LastIndexOf + @IndexOfPartial
                ,@Occurrence = @Occurrence - 1
                ,@Iteration = @Iteration + 1

        IF (@Occurrence = 0) BREAK;
    END

    SELECT @LastIndexOf = @OriginalLength - @LastIndexOf + 1 -- Invert due to reverse
    RETURN @LastIndexOf 
END
GO

GRANT EXECUTE ON GetLastIndexOf TO public
GO

这是我的测试用例，已通过

SELECT dbo.GetLastIndexOf('f','123456789\123456789\', 1) as indexOf -- expect 0 (no instances)
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 1) as indexOf -- expect 20
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 2) as indexOf -- expect 10
SELECT dbo.GetLastIndexOf('\','1234\6789\123456789\', 3) as indexOf -- expect 5

我在搜索解决类似问题的方案时遇到了这个帖子，它有完全相同的要求，但是针对的是另一种缺少REVERSE函数的数据库。

在我的情况下，这是针对一个稍微不同语法的OpenEdge (Progress)数据库。这使得INSTR函数对我可用，大多数Oracle类型的数据库也提供。

因此，我想出了以下代码：

SELECT 
  INSTR(foo.filepath, '/',1, LENGTH(foo.filepath) - LENGTH( REPLACE( foo.filepath, '/',  ''))) AS IndexOfLastSlash 
FROM foo

然而，对于我的特定情况（即OpenEdge（Progress）数据库），这并没有产生期望的行为，因为用空字符替换字符后，字符串的长度与原始字符串相同。这对我来说没有太多意义，但我能够通过以下代码绕过问题：

SELECT 
  INSTR(foo.filepath, '/',1, LENGTH( REPLACE( foo.filepath, '/',  'XX')) - LENGTH(foo.filepath))  AS IndexOfLastSlash 
FROM foo

---

现在我明白了，这段代码无法解决 T-SQL 的问题，因为没有替代 INSTR 函数并提供 Occurence 属性的方法。

为了全面起见，我将添加创建此标量函数所需的代码，以便可以像上面的示例一样使用它。

  -- Drop the function if it already exists
  IF OBJECT_ID('INSTR', 'FN') IS NOT NULL
    DROP FUNCTION INSTR
  GO

  -- User-defined function to implement Oracle INSTR in SQL Server
  CREATE FUNCTION INSTR (@str VARCHAR(8000), @substr VARCHAR(255), @start INT, @occurrence INT)
  RETURNS INT
  AS
  BEGIN
    DECLARE @found INT = @occurrence,
            @pos INT = @start;

    WHILE 1=1 
    BEGIN
        -- Find the next occurrence
        SET @pos = CHARINDEX(@substr, @str, @pos);

        -- Nothing found
        IF @pos IS NULL OR @pos = 0
            RETURN @pos;

        -- The required occurrence found
        IF @found = 1
            BREAK;

        -- Prepare to find another one occurrence
        SET @found = @found - 1;
        SET @pos = @pos + 1;
    END

    RETURN @pos;
  END
  GO

为了避免废话，当有可用的REVERSE函数时，您无需创建这个标量函数，只需像这样获取所需结果即可：

SELECT
  LEN(foo.filepath) - CHARINDEX('/', REVERSE(foo.filepath))+1 AS LastIndexOfSlash 
FROM foo

这个答案满足了OP的要求，特别是它允许needle不止是单个字符，并且在haystack中找不到needle时不会生成错误。在我看来，大多数（全部？）其他答案都没有处理这些边缘情况。除此之外，我添加了本地MS SQL服务器CharIndex函数提供的“起始位置”参数。我试图完全模拟CharIndex的规范，只是从右到左处理而不是从左到右。例如，如果needle或haystack为空，则返回null，如果在haystack中找不到needle，则返回零。有一件事我无法解决，即内置函数的第三个参数是可选的。对于SQL Server用户定义的函数，必须在调用中提供所有参数，除非使用“EXEC”调用函数。虽然第三个参数必须包含在参数列表中，但您可以提供关键字“default”作为占位符，而无需为其赋值（请参见下面的示例）。由于如果需要添加第三个参数比不需要它更容易将其从此函数中删除，因此我在此处包含它作为起点。

create function dbo.lastCharIndex(
 @needle as varchar(max),
 @haystack as varchar(max),
 @offset as bigint=1
) returns bigint as begin
 declare @position as bigint
 if @needle is null or @haystack is null return null
 set @position=charindex(reverse(@needle),reverse(@haystack),@offset)
 if @position=0 return 0
 return (len(@haystack)-(@position+len(@needle)-1))+1
end
go

select dbo.lastCharIndex('xyz','SQL SERVER 2000 USES ANSI SQL',default) -- returns 0
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',default) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',1) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',11) -- returns 1