Trova l'indice dell'ultima occorrenza di una sottostringa utilizzando T-SQL

Modo semplice? No, ma ho usato il contrario. Letteralmente.

Nelle routine precedenti, per trovare l'ultima occorrenza di una determinata stringa, ho utilizzato la funzione REVERSE (), seguito CHARINDEX, seguito di nuovo da REVERSE per ripristinare l'ordine originale. Per esempio:

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

mostra come estrarre i nomi dei file di database effettivi dai loro "nomi fisici", non importa quanto profondamente nidificati nelle sottocartelle. Questo cerca solo un carattere (il backslash), ma puoi costruire su questo per stringhe di ricerca più lunghe.

L'unico svantaggio è che non so quanto bene funzionerà sui tipi di dati TEXT. Sono stato su SQL 2005 per alcuni anni e non sono più abituato a lavorare con TEXT - ma mi sembra di ricordare che potresti usare LEFT e RIGHT su di esso?

Philip

Il modo più semplice è ....

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

Se si utilizza Sqlserver 2005 o versione successiva, l'utilizzo della funzione REVERSE molte volte è dannoso per le prestazioni, di seguito il codice è più efficiente.

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

Questo ha funzionato molto bene per me.

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

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

ha funzionato meglio per me

Domanda vecchia ma ancora valida, ecco qui cosa ho creato in base alle informazioni fornite da altri qui.

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

Hmm, so che questo è un thread vecchio, ma un Tally Table potrebbe farlo in SQL2000 (o in qualsiasi altro 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

Una tabella di controllo è solo una tabella di numeri incrementali.

Il substring(@str, _Tally.n, 1) = @delim ottiene la posizione di ciascun delimitatore, quindi si ottiene la posizione massima in quel set.

I tavoli di riscontro sono fantastici. Se non li hai mai usati, c'è un buon articolo su SQL Server Central (Reg Free, o semplicemente usa Bug Me Not ( http://www.bugmenot.com/view/sqlservercentral.com )).

* EDIT: Rimosso n <= LEN(TEXT_FIELD), poiché non è possibile utilizzare LEN () sul tipo di TEXT. Finché il substring(...) = @delim rimane sebbene il risultato sia ancora corretto.

Invertire sia la stringa che la sottostringa, quindi cercare la prima occorrenza.

Mi rendo conto che questa è una domanda vecchia di diversi anni, ma ...

Su Access 2010, puoi usare InStrRev() per fare questo. Spero che questo ti aiuti.

Alcune delle altre risposte restituiscono una stringa effettiva mentre io avevo più bisogno di conoscere l'indice reale int. E le risposte che sembrano complicano troppo le cose. Usando alcune delle altre risposte come ispirazione, ho fatto quanto segue ...

Innanzitutto, ho creato una funzione:

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

Quindi, nella tua domanda puoi semplicemente fare questo:

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

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

Quanto sopra dovrebbe restituire 23 (l'ultimo indice di ':')

Spero che questo lo abbia reso un po 'più facile per qualcuno!

Questa risposta utilizza MS SQL Server 2008 (non ho accesso a MS SQL Server 2000), ma il modo in cui lo vedo in base all'OP sono 3 situazioni da prendere in considerazione. Da quello che ho provato, nessuna risposta qui riguarda tutti e 3:

1. Restituisce l'ultimo indice di un carattere di ricerca in una determinata stringa.
2. Restituisce l'ultimo indice di una sottostringa di ricerca (più di un singolo carattere ) In una determinata stringa.
3. Se il carattere di ricerca o la sottostringa non si trova nella stringa specificata, restituisce 0

La funzione che ho trovato prende 2 parametri:

@String NVARCHAR(MAX): la stringa da cercare

@FindString NVARCHAR(MAX): un singolo carattere o una sottostringa per ottenere l'ultimo indice di in @String

Restituisce un INT che è l'indice positivo di @FindString in @String o 0 che significa che @FindString non è in @String

Ecco una spiegazione di cosa fa la funzione:

1. Inizializza @ReturnVal in 0 indicando che @FindString non è in @String
2. Controlla l'indice di @FindString in @String usando CHARINDEX() 
3. Se l'indice di @FindString in @String è 0, @ReturnVal è lasciato come 0
4. Se l'indice di @FindString in @String è > 0, @FindString è in @String so Calcola l'ultimo indice di @FindString in @String utilizzando REVERSE()
5. Restituisce @ReturnVal che è o un numero positivo che è l'ultimo indice di @FindString in @String o 0 che indica che @FindString non è in @String

Ecco lo script della funzione di creazione (copia e incolla pronto):

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

Ecco un po 'che verifica comodamente la funzione:

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)

Ho solo eseguito questo su MS SQL Server 2008 perché non ho accesso a qualsiasi altra versione, ma da quello che ho esaminato questo dovrebbe essere buono almeno per 2008+.

Godere.

So che sarà inefficiente, ma hai considerato la possibilità di trasmettere il campo text a varchar in modo che tu possa utilizzare la soluzione fornita dal sito web che hai trovato? So che questa soluzione creerebbe problemi in quanto potreste potenzialmente troncare il record se la lunghezza nel campo text ha superato la lunghezza del vostro varchar (per non dire che non sarebbe molto performante).

Poiché i tuoi dati si trovano all'interno di un campo text (e stai utilizzando SQL Server 2000), le tue opzioni sono limitate.

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

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

Non sono stati testati, potrebbe essere fuori da uno a causa dell'indice zero, ma funziona nella funzione SUBSTRING quando si taglia fuori da @indexOf caratteri alla fine della stringa

SUBSTRING([MyField], 0, @LastIndexOf)

Questa risposta soddisfa i requisiti dell'OP. in particolare consente all'ago di essere più di un singolo carattere e non genera un errore quando l'ago non viene trovato nel pagliaio. Mi è sembrato che la maggior parte (tutte?) Delle altre risposte non gestisse quei casi Edge. Oltre a ciò ho aggiunto l'argomento "Posizione iniziale" fornito dalla funzione CharIndex del server MS SQL nativo. Ho provato a rispecchiare esattamente le specifiche per CharIndex tranne che per elaborare da destra a sinistra anziché da sinistra a destra. es. restituisco null se ago o pagliaio sono nulli e restituisco zero se l'ago non viene trovato nel pagliaio. Una cosa che non ho potuto evitare è che con la funzione integrata il terzo parametro è opzionale. Con le funzioni definite dall'utente di SQL Server, tutti i parametri devono essere forniti nella chiamata a meno che la funzione non venga chiamata utilizzando "EXEC". Mentre il terzo parametro deve essere incluso nell'elenco dei parametri, è possibile fornire la parola chiave "default" come segnaposto per esso senza dover dargli un valore (vedere esempi di seguito). Dal momento che è più facile rimuovere il terzo parametro da questa funzione se non desiderato rispetto a quello sarebbe di aggiungerlo se necessario, l'ho incluso qui come punto di partenza.

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

Per ottenere la parte prima dell'ultima occorrenza del delimitatore (funziona solo per NVARCHAR a causa dell'utilizzo DATALENGTH):

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

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

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

Questo codice funziona anche se la sottostringa contiene più di 1 carattere.

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

Mi sono imbattuto in questo thread mentre cercavo una soluzione al mio problema simile che aveva lo stesso identico requisito ma era per un diverso tipo di database che mancava anche della funzione REVERSE.

Nel mio caso questo era per un OpenEdge (Progress) database, che ha una sintassi leggermente diversa. Ciò ha reso disponibile la funzione INSTR che la maggior parte dei database Oracle digitati offre .

Quindi ho trovato il seguente codice:

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

Tuttavia, per la mia situazione specifica (essendo il database OpenEdge (Progress) questo non ha comportato il comportamento desiderato perché la sostituzione del carattere con un carattere vuoto ha dato la stessa lunghezza della stringa originale. Questo non ha molto senso per me, ma sono stato in grado di aggirare il problema con il codice qui sotto:

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

Ora capisco che questo codice non risolverà il problema per T-SQL perché non esiste un'alternativa alla funzione INSTR che offre la proprietà Occurence.

Per essere accurato, aggiungo il codice necessario per creare questa funzione scalare in modo che possa essere utilizzata allo stesso modo come ho fatto negli esempi sopra.

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

Per evitare l'ovvio, quando è disponibile la funzione REVERSE non è necessario creare questa funzione scalare e si può semplicemente ottenere il risultato richiesto in questo modo:

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

Avevo bisogno di trovare l'ennesima ultima posizione di una barra rovesciata in un percorso di cartella . Ecco la mia soluzione.

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

Ecco i miei casi di prova che passano

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

Se si desidera ottenere l'indice dell'ultimo spazio in una stringa di parole, è possibile utilizzare questa espressione RIGHT (nome, (CHARINDEX ('', REVERSE (nome), 0)) per restituire l'ultima parola nel stringa.Questo è utile se si desidera analizzare il cognome di un nome completo che include le iniziali per il primo e/o secondo nome.