Znajdź indeks ostatniego wystąpienia podciągu za pomocą T-SQL

Prosty sposób? Nie, ale użyłem odwrotu. Dosłownie.

W poprzednich procedurach, aby znaleźć ostatnie wystąpienie danego ciągu znaków, użyłem funkcji REVERSE (), następnie CHARINDEX, a następnie ponownie REVERSE, aby przywrócić pierwotną kolejność. Na przykład:

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

Pokazuje, jak wyodrębnić rzeczywiste nazwy plików bazy danych z ich "nazw fizycznych", bez względu na to, jak głęboko zagnieżdżone są w podfolderach. To nie szuka tylko jednego znaku (ukośnik wsteczny), ale można budować na tym dla dłuższych ciągów wyszukiwania.

Jedynym minusem jest to, że nie wiem, jak dobrze to będzie działać na typach danych tekstowych. Jestem na SQL 2005 od kilku lat i nie jestem już zaznajomiony z pracą z tekstem - ale zdaje mi się, że można na nim używać lewej i prawej strony?

Filip

Najprostszym sposobem jest....

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

Jeśli używasz SQLServer 2005 lub nowszego, używanie funkcji REVERSE wiele razy jest szkodliwe dla wydajności, poniższy kod jest bardziej efektywny.

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

To działało bardzo dobrze dla mnie.

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

Stare, Ale wciąż aktualne pytanie, więc oto, co stworzyłem na podstawie informacji dostarczonych przez innych tutaj.

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

Pracował dla mnie lepiej

Hmm, wiem, że to stary wątek, ale tabelka tally mogłaby to zrobić w SQL2000 (lub innej bazie danych):

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

Tabela tally jest tylko tabelą liczb rosnących.

substring(@str, _Tally.n, 1) = @delim pobiera pozycję każdego ogranicznika, wtedy otrzymujesz maksymalną pozycję w tym zbiorze.

Tabele Tally są niesamowite. Jeśli nie używałeś ich wcześniej, jest dobry artykuł na SQL Server Central (Free reg, or just use Bug Me Not ( http://www.bugmenot.com/view/sqlservercentral.com)).

*EDIT: Removed n <= LEN(TEXT_FIELD), ponieważ nie możesz użyć len () na typie tekstu. Dopóki substring(...) = @delim pozostaje, chociaż wynik jest nadal poprawny.

Odwróć zarówno łańcuch, jak i podłańcuch, a następnie wyszukaj pierwsze wystąpienie.

Zdaję sobie sprawę, że to pytanie sprzed kilku lat, ale...

Na Access 2010, możesz użyć InStrRev(), aby to zrobić. Mam nadzieję, że to pomoże.

Wiem, że będzie to nieefektywne, ale czy rozważałeś przeniesienie pola text na varchar, aby móc skorzystać z rozwiązania dostarczonego przez znalezioną stronę internetową? Wiem, że to rozwiązanie spowodowałoby problemy, ponieważ potencjalnie można by obciąć rekord, gdyby długość w polu text przekroczyła długość twojego varchar (nie wspominając o tym, że nie byłoby to zbyt wydajne).

Ponieważ Twoje dane znajdują się w polu text (a używasz SQL Server 2000), Twoje opcje są ograniczone.

Jeśli chcesz uzyskać indeks ostatniej spacji w ciągu słów, możesz użyć tego wyrażenia RIGHT (name, (CHARINDEX (' ' , REVERSE (name),0)), aby zwrócić ostatnie słowo w łańcuchu. Jest to pomocne, jeśli chcesz przeanalizować nazwisko pełnego Imienia, które zawiera Inicjały pierwszego i / lub drugiego imienia.

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

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

Nie testowałem, może być wyłączony o jeden z powodu zerowego indeksu, ale działa w funkcji SUBSTRING podczas odcinania znaków @indexOf na koniec łańcucha

SUBSTRING([MyField], 0, @LastIndexOf)

Musiałem znaleźć N-tą ostatnią pozycję ukośnika wstecznego w ścieżce folderu. Oto moje rozwiązanie.

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

Oto moje testy, które przechodzą

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

Aby uzyskać część przed ostatnim wystąpieniem ogranicznika (działa tylko dla NVARCHAR ze względu na użycie DATALENGTH):

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

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

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

Niektóre z innych odpowiedzi zwracają rzeczywisty ciąg znaków, podczas gdy ja miałem więcej potrzeby znać rzeczywisty indeks int. A odpowiedzi, które to robią, wydają się nadmiernie komplikować rzeczy. Korzystając z innych odpowiedzi jako inspiracji, zrobiłem następujące rzeczy...

Najpierw stworzyłem funkcję:

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

Następnie w zapytaniu możesz po prostu zrobić to:

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

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

Powyższe powinno zwrócić 23 (ostatni indeks':')

Mam nadzieję, że to ułatwiło komuś życie!

Ta odpowiedź spełnia wymagania OP. w szczególności pozwala igle być więcej niż jednym znakiem i nie generuje błędu, gdy igła nie znajduje się w stogu siana. Wydawało mi się, że najbardziej(Wszystkie?) z pozostałych odpowiedzi nie zajmował się tymi przypadkami. Poza tym dodałem argument "pozycja wyjściowa" dostarczany przez natywną funkcję MS SQL server CharIndex. Próbowałem dokładnie odzwierciedlać specyfikację CharIndex, z wyjątkiem przetwarzania od prawej do lewej zamiast od lewej na prawo. na przykład zwracam null, jeśli igła lub stóg siana jest null i zwracam zero, jeśli igła nie znajduje się w stogu siana. Jedną rzeczą, której nie mogłem obejść, jest to, że z wbudowaną funkcją trzeci parametr jest opcjonalny. W przypadku funkcji zdefiniowanych przez użytkownika SQL Server wszystkie parametry muszą być podane w wywołaniu, chyba że funkcja jest wywoływana za pomocą "EXEC". Podczas gdy trzeci parametr musi być zawarty na liście parametrów, możesz podać słowo kluczowe "default" jako symbol zastępczy dla niego bez konieczności podaj jej wartość (patrz przykłady poniżej). Ponieważ łatwiej jest usunąć trzeci parametr z tej funkcji, jeśli nie jest to pożądane, niż byłoby dodać go w razie potrzeby, umieściłem go tutaj jako punkt wyjścia.

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

Natknąłem się na ten wątek szukając rozwiązania mojego podobnego problemu, który miał dokładnie takie same wymagania, ale był dla innego rodzaju bazy danych, która również nie miała funkcji REVERSE.

W moim przypadku było to dla OpenEdge (Progress) bazy danych, która ma nieco inną składnię. Dzięki temu dostępna jest dla mnie funkcja INSTR, którą oferuje większość baz danych typu Oracle .

Więc wymyśliłem następujące kod:

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

Jednakże, dla mojej konkretnej sytuacji (jako bazy danych OpenEdge (Progress)) nie spowodowało to pożądanego zachowania, ponieważ zastąpienie znaku pustym znakiem dało taką samą długość jak oryginalny łańcuch. To nie ma dla mnie większego sensu, ale udało mi się ominąć problem z poniższym kodem:

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

Teraz rozumiem, że ten kod nie rozwiąże problemu dla T-SQL ponieważ nie ma alternatywy dla INSTR funkcja oferująca właściwość Occurence.

Aby być dokładnym dodam kod potrzebny do utworzenia tej funkcji skalarnej, aby mogła być używana tak samo jak w powyższych przykładach.

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

Aby uniknąć oczywistego, gdy funkcja REVERSE jest dostępna, nie musisz tworzyć tej funkcji skalarnej i możesz po prostu uzyskać wymagany wynik w następujący sposób:

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

Ten kod działa nawet jeśli podłańcuch zawiera więcej niż 1 znak.

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