sql-server - left - substring index sql

Encuentre el índice de última aparición de una subcadena usando T-SQL

Question

¿Hay una manera directa de encontrar el índice de la última ocurrencia de una cadena usando SQL? Estoy usando SQL Server 2000 en este momento. Básicamente, necesito la funcionalidad que proporciona el método .NET System.String.LastIndexOf . Un poco de google reveló esto - Función para recuperar el último índice - pero eso no funciona si pasas una expresión de columna de "texto". Otras soluciones que se encuentran en otros lugares funcionan solo mientras el texto que estás buscando tenga 1 carácter.

Probablemente tendré que cocinar una función. Si lo hago, lo publicaré aquí para que la gente pueda verlo y quizás hacer uso de él.

Answer 1

¿De manera directa? No, pero he usado el reverso. Literalmente.

En las rutinas anteriores, para encontrar la última ocurrencia de una cadena dada, utilicé la función REVERSE (), seguí CHARINDEX, y seguí otra vez con REVERSE para restablecer el orden original. Por ejemplo:

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

muestra cómo extraer los nombres de los archivos de la base de datos real de sus "nombres físicos", sin importar qué tan anidados estén en las subcarpetas. Esto busca solo un carácter (la barra invertida), pero puede construir sobre esto para cadenas de búsqueda más largas.

El único inconveniente es que no sé qué tan bien funcionará esto en los tipos de datos TEXT. He estado en SQL 2005 durante algunos años, y ya no estoy familiarizado con el trabajo con TEXT, pero me parece recordar que podría usar IZQUIERDA y DERECHA en él.

Felipe

Answer 2

Algunas de las otras respuestas devuelven una cadena real, mientras que yo tenía más necesidad de conocer el índice real int. Y las respuestas que hacen eso parecen complicar demasiado las cosas. Usando algunas de las otras respuestas como inspiración, hice lo siguiente ...

Primero, creé una función:

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

Luego, en su consulta, simplemente puede hacer esto:

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

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

Lo anterior debe devolver 23 (el último índice de '':'')

¡Espero que esto sea un poco más fácil para alguien!

Answer 3

Encontré este hilo mientras buscaba una solución para mi problema similar que tenía exactamente el mismo requisito pero era para un tipo diferente de base de datos que también carecía de la función REVERSE .

En mi caso esto fue para una base de datos OpenEdge (Progress) , que tiene una sintaxis ligeramente diferente. Esto hizo que la función INSTR disponible para mí que ofrecen la mayoría de las bases de datos tipadas por Oracle .

Así que se me ocurrió el siguiente código:

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

Sin embargo, para mi situación específica (al ser la base de datos OpenEdge (Progress) ), esto no dio como resultado el comportamiento deseado porque reemplazar el carácter con un carácter vacío dio la misma longitud que la cadena original. Esto no tiene mucho sentido para mí, pero pude eludir el problema con el siguiente código:

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

Ahora entiendo que este código no resolverá el problema para T-SQL porque no hay alternativa a la función INSTR que ofrece la propiedad INSTR .

Para ser exhaustivo, agregaré el código necesario para crear esta función escalar, de modo que pueda usarse de la misma forma que lo hice en los ejemplos anteriores.

  -- 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 lo obvio, cuando la función REVERSE está disponible, no necesita crear esta función escalar y puede obtener el resultado requerido de esta manera:

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

Answer 4

Está limitado a una pequeña lista de funciones para el tipo de datos de texto.

Todo lo que puedo sugerir es comenzar con PATINDEX , pero trabajar hacia atrás desde DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc. hasta que obtenga un resultado o termine en cero (DATALENGTH-DATALENGTH)

Esto realmente es algo que SQL Server 2000 simplemente no puede manejar.

Editar para otras respuestas : REVERSE no está en la lista de funciones que se pueden usar con datos de texto en SQL Server 2000

Answer 5

Esta respuesta cumple con los requisitos de OP. específicamente permite que la aguja sea más que un solo carácter y no genera un error cuando la aguja no se encuentra en el pajar. Me pareció que la mayoría (¿todas?) De las otras respuestas no manejaban esos casos extremos. Más allá de eso, agregué el argumento "Posición inicial" proporcionado por la función CharIndex del servidor MS SQL nativo. Traté de duplicar exactamente las especificaciones de CharIndex excepto para procesar de derecha a izquierda en lugar de izquierda a derecha. Por ejemplo, devuelvo nulo si la aguja o el pajar son nulos y devuelvo el cero si no se encuentra la aguja en el pajar. Una cosa que no pude evitar es que con la función incorporada, el tercer parámetro es opcional. Con las funciones definidas por el usuario de SQL Server, se deben proporcionar todos los parámetros en la llamada a menos que se llame a la función utilizando "EXEC". Si bien el tercer parámetro debe incluirse en la lista de parámetros, puede proporcionar la palabra clave "predeterminada" como marcador de posición sin tener que darle un valor (ver ejemplos a continuación). Dado que es más fácil eliminar el tercer parámetro de esta función si no es el deseado de lo que sería agregarlo si fuera necesario, lo he incluido aquí como punto 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

Answer 6

Esto funcionó muy bien para mí.

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

Answer 7

Hmm, sé que este es un hilo viejo, pero una tabla de recuento podría hacer esto en SQL2000 (o en cualquier otra base de datos):

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

Una tabla de conteo es solo una tabla de incremento de números.

La substring(@str, _Tally.n, 1) = @delim obtiene la posición de cada delimitador, luego solo obtiene la posición máxima en ese conjunto.

Las tablas de Tally son increíbles. Si no los ha usado antes, hay un buen artículo en SQL Server Central (Reg. Gratis, o simplemente use Bug Me Not ( http://www.bugmenot.com/view/sqlservercentral.com )).

* EDITAR: eliminó n <= LEN(TEXT_FIELD) , ya que no puede usar LEN () en el tipo de texto. Mientras la substring(...) = @delim permanezca, el resultado sigue siendo correcto.

Answer 8

Invierta su cadena y su subcadena, luego busque la primera ocurrencia.

Answer 9

La forma más simple es ...

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

Answer 10

Me doy cuenta de que esta es una pregunta de varios años, pero ...

En Access 2010 , puede usar InStrRev() para hacer esto. Espero que esto ayude.

Answer 11

Necesitaba encontrar la enésima posición de una barra invertida en una ruta de carpeta. Aquí está mi solución.

/*
http://.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

Aquí están mis casos de prueba que pasan

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

Answer 12

Para obtener la pieza antes de la última ocurrencia del delimitador (funciona solo para NVARCHAR debido al uso de DATALENGTH ):

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

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

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

Answer 13

Sé que será ineficiente, pero ¿ha considerado enviar el campo de text a varchar para que pueda usar la solución provista por el sitio web que encontró? Sé que esta solución crearía problemas, ya que podría truncar el registro si la longitud en el campo de text desbordara la longitud de su varchar (sin mencionar que no sería muy eficiente).

Como sus datos están dentro de un campo de text (y está usando SQL Server 2000), sus opciones son limitadas.

Answer 14

Si desea obtener el índice del último espacio en una cadena de palabras, puede usar esta expresión RIGHT (nombre, (CHARINDEX ('''', REVERSE (nombre), 0)) para devolver la última palabra en la cadena. es útil si desea analizar el apellido de un nombre completo que incluya las iniciales para el primer y / o segundo nombre.

Answer 15

Si está utilizando Sqlserver 2005 o superior, usar la función REVERSE muchas veces es perjudicial para el rendimiento, el siguiente código es más 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

Answer 16

Una pregunta anterior pero válida, así que aquí está lo que creé en base a la información proporcionada por otros aquí.

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

Answer 17

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

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

No lo ha probado, podría estar apagado en uno debido al índice cero, pero funciona en la función SUBSTRING al cortar desde @indexOf caracteres hasta el final de su cadena

SUBSTRING([MyField], 0, @LastIndexOf)

Answer 18

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

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

Answer 19

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

funcionó mejor para mí