Encontre o índice da última ocorrência de uma subcadeia usando T-SQL

Existe uma maneira simples de encontrar o índice da última ocorrência de uma string usando SQL? Estou usando o SQL Server 2000 agora. Eu basicamente preciso da funcionalidade que o método .NET System.String.LastIndexOf fornece. Um pequeno googling revelou isso – Função para recuperar o último índice – mas isso não funciona se você passar uma expressão de coluna “texto”. Outras soluções encontradas em outros lugares funcionam apenas enquanto o texto que você está pesquisando tiver 1 caractere.

Eu provavelmente terei que cozinhar uma function. Se eu fizer isso, vou postar aqui para que vocês possam olhar e talvez fazer uso.

Você está limitado a uma pequena lista de funções para o tipo de dados de texto.

Tudo o que posso sugerir é começar com PATINDEX , mas trabalhe de trás para a frente de DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc até obter um resultado ou terminar em zero (DATALENGTH-DATALENGTH)

Isso realmente é algo que o SQL Server 2000 simplesmente não pode manipular.

Editar para outras respostas : REVERSE não está na lista de funções que podem ser usadas com dados de texto no SQL Server 2000

Maneira direta? Não, mas usei o contrário. Literalmente.

Nas rotinas anteriores, para encontrar a última ocorrência de uma determinada string, usei a function REVERSE (), seguida de CHARINDEX, seguida novamente de REVERSE para restaurar a ordem original. Por exemplo:

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

mostra como extrair os nomes reais dos arquivos do database de seus “nomes físicos”, não importando o quão profundamente nesteds em subpastas. Isso procura apenas um caractere (a barra invertida), mas você pode criar isso para sequências de pesquisa mais longas.

A única desvantagem é que não sei como isso funcionará nos tipos de dados TEXT. Eu estive no SQL 2005 por alguns anos agora, e não estou mais familiarizado com o trabalho com o TEXT – mas parece que me lembro que você poderia usar LEFT e RIGHT nele?

Philip

A maneira mais simples é ….

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

Se você estiver usando o Sqlserver 2005 ou superior, usar a function REVERSE muitas vezes é prejudicial ao desempenho, o código abaixo é mais eficiente.

 DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words' DECLARE @FindChar VARCHAR(1) = '\' -- Shows text before last slash SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath))) AS Before -- Shows text after last slash SELECT RIGHT(@FilePath, CHARINDEX(@FindChar,REVERSE(@FilePath))-1) AS After -- Shows the position of the last slash SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt 
 DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words' DECLARE @FindChar VARCHAR(1) = '\' SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt 

Isso funcionou muito bem para mim.

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

Pergunta antiga, mas ainda válida, então aqui está o que eu criei com base nas informações fornecidas por outras pessoas aqui.

 create function fnLastIndexOf(@text varChar(max),@char varchar(1)) returns int as begin return len(@text) - charindex(@char, reverse(@text)) -1 end 
 REVERSE(SUBSTRING(REVERSE(ap_description),CHARINDEX('.',REVERSE(ap_description)),len(ap_description))) 

funcionou melhor para mim

Hmm, eu sei que este é um thread antigo, mas uma tabela de contagem pode fazer isso no SQL2000 (ou qualquer outro database):

 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 

Uma tabela de contagem é apenas uma tabela de números incrementais.

A substring(@str, _Tally.n, 1) = @delim obtém a posição de cada delimitador e, em seguida, você obtém a posição máxima nesse conjunto.

Mesas de Tally são impressionantes. Se você não os usou antes, há um bom artigo no SQL Server Central (Reg Grátis, ou apenas use o Bug Me Not ( http://www.bugmenot.com/view/sqlservercentral.com )).

* EDIT: Removido n <= LEN(TEXT_FIELD) , como você não pode usar LEN () no tipo TEXTO. Contanto que a substring(...) = @delim permaneça, embora o resultado ainda esteja correto.

Inverta a sequência e a substring e pesquise a primeira ocorrência.

Eu percebo que esta é uma questão de vários anos, mas …

No Access 2010 , você pode usar InStrRev() para fazer isso. Espero que isto ajude.

Eu sei que será ineficiente, mas você já pensou em lançar o campo de text para varchar para poder usar a solução fornecida pelo site que encontrou? Eu sei que esta solução criaria problemas como você poderia truncar o registro se o comprimento no campo de text transbordou o comprimento do seu varchar (para não mencionar que não seria muito performant).

Como seus dados estão dentro de um campo de text (e você está usando o SQL Server 2000), suas opções são limitadas.

Se você quiser obter o índice do último espaço em uma string de palavras, você pode usar esta expressão RIGHT (nome, (CHARINDEX (”, REVERSE (name), 0)) para retornar a última palavra na string. é útil se você quiser analisar o sobrenome de um nome completo que inclua iniciais para o primeiro e / ou nome do meio.

@indexOf =

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

Não testei, ele pode estar desativado por causa de um índice zero, mas funciona na function SUBSTRING ao cortar caracteres @indexOf no final da string

SUBSTRING([MyField], 0, @LastIndexOf)

Eu precisava encontrar a última posição de uma barra invertida em um caminho de pasta. Aqui está a minha solução.

 /* 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 

Aqui estão os meus casos de teste que passam

 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 

Para obter a peça antes da última ocorrência do delimitador (funciona apenas para NVARCHAR devido ao uso do DATALENGTH ):

 DECLARE @Fullstring NVARCHAR(30) = '12.345.67890.ABC'; DECLARE @Delimiter CHAR(1) = '.'; SELECT SUBSTRING(@Fullstring, 1, DATALENGTH(@Fullstring)/2 - CHARINDEX(@Delimiter, REVERSE(@Fullstring))); 

Algumas das outras respostas retornam uma string real, enquanto eu tive mais necessidade de saber o índice real int. E as respostas que fazem isso parecem complicar demais as coisas. Usando algumas das outras respostas como inspiração, fiz o seguinte …

Primeiro, criei uma function:

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

Então, na sua consulta, você pode simplesmente fazer isso:

 declare @stringToSearch varchar(max) = 'SomeText: SomeMoreText: SomeLastText' select dbo.LastIndexOf(':', @stringToSearch) 

O acima deve retornar 23 (o último índice de ‘:’)

Espero que isso seja um pouco mais fácil para alguém!

Esta resposta atende aos requisitos do OP. especificamente, permite que a agulha seja mais do que um único caractere e não gera um erro quando a agulha não é encontrada no palheiro. Pareceu-me que a maioria (todas?) Das outras respostas não lidou com esses casos extremos. Além disso, adicionei o argumento “Posição Inicial” fornecido pela function CharIndex do servidor MS SQL nativo. Eu tentei espelhar exatamente a especificação do CharIndex, exceto para processar da direita para a esquerda, em vez da esquerda para a direita. Por exemplo, eu retorno null se agulha ou palheiro for nulo e eu retornar zero se agulha não for encontrada no palheiro. Uma coisa que eu não consegui entender é que, com a function interna, o terceiro parâmetro é opcional. Com as funções definidas pelo usuário do SQL Server, todos os parâmetros devem ser fornecidos na chamada, a menos que a function seja chamada usando “EXEC”. Enquanto o terceiro parâmetro deve ser incluído na lista de parâmetros, você pode fornecer a palavra-chave “default” como um espaço reservado para isso sem ter que dar um valor a ele (veja os exemplos abaixo). Uma vez que é mais fácil remover o terceiro parâmetro desta function, se não for desejado, será necessário incluí-lo aqui como ponto de partida.

 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 

Eu me deparei com este segmento enquanto procurava uma solução para o meu problema semelhante, que tinha exatamente o mesmo requisito, mas era para um tipo diferente de database que também estava faltando a function REVERSE .

No meu caso, isso era para um database OpenEdge (Progress) , que tem uma syntax ligeiramente diferente. Isso disponibilizou a function INSTR para mim que a maioria dos bancos de dados typescripts da Oracle oferece .

Então eu criei o seguinte código:

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

No entanto, para minha situação específica (sendo o database OpenEdge (Progress) ), isso não resultou no comportamento desejado porque a substituição do caractere por um caractere vazio deu o mesmo tamanho da string original. Isso não faz muito sentido para mim, mas eu pude contornar o problema com o código abaixo:

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

Agora eu entendo que esse código não vai resolver o problema para o T-SQL porque não há alternativa para a function INSTR que oferece a propriedade Occurence .

Só para ser aprofundado, adicionarei o código necessário para criar essa function escalar para que ela possa ser usada da mesma forma como fiz nos exemplos acima.

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

Para evitar o óbvio, quando a function REVERSE estiver disponível, você não precisa criar esta function escalar e você pode simplesmente obter o resultado desejado da seguinte forma:

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

Esse código funciona mesmo se a substring contiver mais de 1 caractere.

 DECLARE @FilePath VARCHAR(100) = 'My_sub_Super_sub_Long_sub_String_sub_With_sub_Long_sub_Words' DECLARE @FindChar VARCHAR(5) = '_sub_' -- Shows text before last slash SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(REVERSE(@FindChar), REVERSE(@FilePath)) - LEN(@FindChar) + 1) AS Before -- Shows text after last slash SELECT RIGHT(@FilePath, CHARINDEX(REVERSE(@FindChar), REVERSE(@FilePath)) -1) AS After -- Shows the position of the last slash SELECT LEN(@FilePath) - CHARINDEX(REVERSE(@FindChar), REVERSE(@FilePath)) AS LastOccuredAt 
    Intereting Posts