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

有没有使用 SQL 查找字符串最后一次出现的索引的简单方法？我现在正在使用 SQLServer2000。我基本上需要的功能。NET System.String.LastIndexOf方法提供。稍微在谷歌上搜索一下就发现了这一点—— 检索最后索引的函数——但是如果你传递一个“ text”列表达式，那就不起作用了。在其他地方找到的其他解决方案只能在您正在搜索的文本为1个字符长的情况下工作。

我可能需要创建一个函数。如果我这样做了，我会把它贴在这里，这样你们可以看看，也许利用。

459451 次浏览
小开

我知道这将是效率低下，但你有没有考虑铸造的 text字段到 varchar，以便您可以使用的解决方案所提供的网站，你发现？我知道这个解决方案会产生问题，因为如果 text字段的长度超过了 varchar的长度，您可能会截断记录(更不用说它的性能不会很好)。

由于您的数据位于 text字段中(并且您正在使用 SQLServer2000) ，因此您的选择是有限的。

小开
最佳答案

对于文本数据类型，限制为 函数的小列表。

我所能建议的是从 PATINDEX开始，但是从 DATALENGTH-1, DATALENGTH-2, DATALENGTH-3等向后工作，直到你得到一个结果或结束在零(DATALENGTH-DATALENGTH)

这确实是 SQL Server 2000无法处理的问题。

编辑其他答案 : 在 SQLServer2000中，REVERSE 不在可用于文本数据的函数列表中

小开

反转字符串和子字符串，然后搜索第一个匹配项。

小开

直截了当? 不，但我用了相反的方法，真的。

在以前的例程中，为了找到给定字符串的最后一次出现，我使用了 REVERSE ()函数，然后是 CHARINDEX，再次使用了 REVERSE 来恢复原来的顺序。例如:

SELECT
mf.name
,mf.physical_name
,reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))
from sys.master_files mf

演示如何从它们的“物理名称”中提取实际的数据库文件名，无论它们在子文件夹中嵌套得多深。这只搜索一个字符(反斜杠) ，但是您可以在此基础上构建更长的搜索字符串。

唯一的缺点是，我不知道这对于 TEXT 数据类型的效果如何。我已经使用 SQL 2005好几年了，不再熟悉使用 TEXT ——但是我似乎记得您可以在它上面使用 LEFT 和 RIGHT？

菲利普

小开

嗯，我知道这是一个老线程，但是在 SQL2000(或任何其他数据库)中，一个统计表可以做到这一点:

DECLARE @str CHAR(21),
@delim CHAR(1)
SELECT @str = 'Your-delimited-string',
@delim = '-'


SELECT
MAX(n) As 'position'
FROM
dbo._Tally
WHERE
substring(@str, _Tally.n, 1) = @delim

计数表只是一个递增数字的表。

substring(@str, _Tally.n, 1) = @delim得到每个分隔符的位置，然后得到该集合中的最大位置。

理货表非常棒。如果你以前没有用过它们，有一篇关于 SQLServer 中心的好文章。

* 编辑: 删除 n <= LEN(TEXT_FIELD)，因为不能在文本类型上使用 LEN ()。只要 substring(...) = @delim仍然存在，虽然结果仍然是正确的。

小开

最简单的方法是..。

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))
小开

旧的，但仍然有效的问题，所以这里我创建的基础上提供的信息，其他人在这里。

create function fnLastIndexOf(@text varChar(max),@char varchar(1))
returns int
as
begin
return len(@text) - charindex(@char, reverse(@text)) -1
end
小开

如果要获取单词字符串中最后一个空格的索引，可以使用此表达式 右键(name，(CHARINDEX (’，REVERSE (name) ，0))返回字符串中的最后一个单词。如果您希望解析出包含首字母缩写和/或中间名的全名的姓，这将很有帮助。

小开 
REVERSE(SUBSTRING(REVERSE(ap_description),CHARINDEX('.',REVERSE(ap_description)),len(ap_description)))

对我来说效果更好

小开

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

在 Access 2010上，你可以使用 InStrRev()来做这件事。希望这对你有帮助。

小开

如果您正在使用 Sqlserver2005或更高版本，多次使用 REVERSE函数会损害性能，下面的代码更有效。

DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'


-- text before last slash
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath))) AS Before
-- text after last slash
SELECT RIGHT(@FilePath, CHARINDEX(@FindChar,REVERSE(@FilePath))-1) AS After
-- the position of the last slash
SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) + 1 AS LastOccuredAt
小开

@indexOf = <whatever characters you are searching for in your string>

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

还没有测试过，它可能因为索引为零而偏离了一个，但是在从 @indexOf字符到字符串末尾截断时，它在 SUBSTRING函数中工作

SUBSTRING([MyField], 0, @LastIndexOf)

小开

这招对我很管用。

REVERSE(SUBSTRING(REVERSE([field]), CHARINDEX(REVERSE('[expr]'), REVERSE([field])) + DATALENGTH('[expr]'), DATALENGTH([field])))
小开

我需要在文件夹路径中找到反斜杠的最后 n 个位置。 这是我的解决办法。

/*
http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql/30904809#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
小开

要在分隔符最后一次出现之前获取零件(由于使用 DATALENGTH，仅适用于 NVARCHAR) :

DECLARE @Fullstring NVARCHAR(30) = '12.345.67890.ABC';


DECLARE @Delimiter CHAR(1) = '.';


SELECT SUBSTRING(@Fullstring, 1, DATALENGTH(@Fullstring)/2 - CHARINDEX(@Delimiter, REVERSE(@Fullstring)));
小开

其他一些答案返回一个实际的字符串，而我更需要知道实际的索引 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(’:’的最后一个索引)

希望这对某些人来说会容易些！

小开

这个答案符合 OP 的要求。具体来说，它允许针超过一个单一的字符，它不会产生一个错误，当针没有发现在干草堆。在我看来，最重要的(全部?)其他答案中没有处理这些边缘情况。除此之外，我还添加了由本机 MSSQL 服务器 CharIndex 函数提供的“ Startposition”参数。除了从右到左而不是从左到右处理之外，我试图精确地镜像 CharIndex 的规范。如果针或干草堆是空的，我返回零; 如果在干草堆中没有找到针，我返回零。我无法回避的一点是，在内置函数中，第三个参数是可选的。对于 SQLServer 用户定义的函数，必须在调用中提供所有参数，除非使用“ 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
小开 
DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'


SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt
小开

我遇到这个线程，而寻找一个解决方案，我的类似问题，有完全相同的要求，但是对于一个不同类型的数据库，也缺乏 REVERSE的功能。

在我的例子中，这是针对 OpenEdge (进度)数据库的，它的语法稍有不同。这使得我可以使用 INSTR函数，即 大多数 Oracle 类型的数据库提供。

所以我想出了以下代码:

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

但是，对于我的特定情况(作为 OpenEdge (进度)数据库) ，这并没有导致所需的行为，因为用空字符替换字符会得到与原始字符串相同的长度。这对我来说没什么意义，但是我可以通过下面的代码绕过这个问题:

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

现在我明白了，这段代码不能解决 T-SQL的问题，因为除了提供 Occurence属性的 INSTR函数之外，没有其他选择。

为了彻底起见，我将添加创建这个标量函数所需的代码，这样就可以像在上面的示例中那样使用它。

  -- 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
小开

即使子字符串包含多于1个字符，此代码也能正常工作。

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
小开

这个答案使用 MS SQL Server 2008(我没有访问 MS SQL Server 2000的权限) ，但是我根据 OP 看到它的方式是需要考虑的3种情况。从我试过的答案来看，这里没有答案，涵盖了所有三个:

  1. 返回给定字符串中搜索字符的最后一个索引。
  2. 返回搜索子字符串的最后一个索引(不仅仅是一个 字符)。
  3. 如果搜索字符或子字符串不在给定的字符串中，则返回 0

我想出的函数有两个参数:

@String NVARCHAR(MAX): 要搜索的字符串

@FindString NVARCHAR(MAX): 获取最后一个字符的单个字符或子字符串 指数在 @String

它返回一个 INT，它是 @String或 0中 @FindString的正指数，这意味着 @FindString不在 @String中

下面解释函数的作用:

  1. 将 @ReturnVal初始化为 0，表示 @FindString不在 @String中
  2. 使用 CHARINDEX()检查 @String中 @FindString的索引
  3. 如果 @String中的 @FindString指数是 0，那么 @ReturnVal就是 0
  4. 如果 @String中的 @FindString指数是 > 0，那么 @FindString就是 @String 利用 REVERSE()计算 @String中 @FindString的最后一个指数
  5. 返回 @ReturnVal，它是一个正数，是 @String或 0中的 @FindString表明 @FindString不在 @String中

下面是 create 函数脚本(复制粘贴就绪) :

CREATE FUNCTION [dbo].[fn_LastIndexOf]
(@String NVARCHAR(MAX)
, @FindString NVARCHAR(MAX))
RETURNS INT
AS
BEGIN
DECLARE @ReturnVal INT = 0
IF CHARINDEX(@FindString,@String) > 0
SET @ReturnVal = (SELECT LEN(@String) -
(CHARINDEX(REVERSE(@FindString),REVERSE(@String)) +
LEN(@FindString)) + 2)
RETURN @ReturnVal
END

这里有一点可以方便地测试函数:

DECLARE @TestString NVARCHAR(MAX) = 'My_sub2_Super_sub_Long_sub1_String_sub_With_sub_Long_sub_Words_sub2_'
, @TestFindString NVARCHAR(MAX) = 'sub'


SELECT dbo.fn_LastIndexOf(@TestString,@TestFindString)

我只在 MSSQLServer2008上运行这个版本，因为我没有访问任何其他版本，但是从我所研究的内容来看，这个版本至少在2008 + 应该是好的。

好好享受吧。

小开

句柄查找长度大于1个字符的内容。 如果你愿意，可以随意增加帕尔玛的大小。

忍不住发帖

drop function if exists lastIndexOf
go
create function lastIndexOf(@searchFor varchar(100),@searchIn varchar(500))
returns int
as
begin


if LEN(@searchfor) > LEN(@searchin) return 0
declare @r varchar(500), @rsp varchar(100)
select @r = REVERSE(@searchin)
select @rsp = REVERSE(@searchfor)
return len(@searchin) - charindex(@rsp, @r) - len(@searchfor)+1
end

和测试

select dbo.lastIndexof('greg','greg greg asdflk; greg sadf' )  -- 18
select dbo.lastIndexof('greg','greg greg asdflk; grewg sadf' )  --5
select dbo.lastIndexof(' ','greg greg asdflk; grewg sadf' ) --24
小开

这个主题已经持续了一段时间，我将提供一个解决方案，涵盖了不同的基础和例子:

declare @aStringData varchar(100) = 'The quick brown/fox jumps/over the/lazy dog.pdf'
/*
The quick brown/fox jumps/over the/lazy dog.pdf
fdp.god yzal/eht revo/spmuj xof/nworb kciuq ehT
*/


select
Len(@aStringData) - CharIndex('/', Reverse(@aStringData)) + 1 [Char Index],
-- Get left side of character, without the character '/'
Left(@aStringData, Len(@aStringData) - CharIndex('/', Reverse(@aStringData))) [Left excluding char],
-- Get left side of character, including the character '/'
Left(@aStringData, Len(@aStringData) - CharIndex('/', Reverse(@aStringData)) + 1) [Left including char],
-- Get right side of character, without the character '/'
Right(@aStringData, CharIndex('/', Reverse(@aStringData)) - 1) [Right including char]

要获得 char 位置，需要在 CharIndex 获得第一个匹配项时反转字符串。请记住，当我们反向时，CharIndex 光标将落在我们找到的字符的另一边。因此，预计补偿为 -1或 + 1，这取决于是想得到字符串的左侧部分还是右侧部分。


提交答案