Trouver l'index de la dernière occurrence d'une sous-chaîne en utilisant T-SQL

Existe-t-il un moyen simple de trouver L'index de la dernière occurrence D'une chaîne en utilisant SQL? J'utilise SQL Server 2000 en ce moment. J'ai fondamentalement besoin de la fonctionnalité que la méthode .NET System.String.LastIndexOf fournit. Un petit googling a révélé ceci - fonction pour récupérer le dernier Index - mais cela ne fonctionne pas si vous passez dans une expression de colonne" texte". Les autres solutions trouvées ailleurs ne fonctionnent que si le texte que vous recherchez est de 1 caractère long.

je vais probablement devoir préparer une fonction. Si je le fais, je le posterai ici pour que vous puissiez le regarder et peut-être l'utiliser.

100
demandé sur Kevin Panko 2009-06-22 03:10:40

20 réponses

Vous êtes limité à petite liste de fonctions pour le type de données texte.

Tout ce que je peux suggérer est de commencer par PATINDEX , mais travailler à l'envers à partir de DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc jusqu'à ce que vous obtenez un résultat ou finir à zéro (DATALENGTH-DATALENGTH)

c'est vraiment quelque chose que SQL Server 2000 ne peut pas supporter.

Modifier d'autres réponses : l'INVERSE n'est pas sur la liste des fonctions qui peut être utilisé avec des données texte dans SQL Server 2000

24
répondu gbn 2015-12-14 07:19:16

sans détour? Non, mais j'ai utilisé l'inverse. Littéralement.

dans les routines précédentes, pour trouver la dernière occurrence d'une chaîne donnée, j'ai utilisé la fonction REVERSE (), suivi de CHARINDEX, suivi de nouveau de REVERSE pour restaurer l'ordre original. Par exemple:

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

montre comment extraire les noms de fichiers réels de la base de données à partir de leurs" noms physiques", peu importe la profondeur imbriquée dans les sous-dossiers. Cela ne cherchez qu'une le caractère (le backslash), mais vous pouvez construire sur cela pour des chaînes de recherche plus longues.

le seul inconvénient est, Je ne sais pas comment cela fonctionnera sur les types de données de texte. Je suis sur SQL 2005 depuis quelques années maintenant, et je ne suis plus habitué à travailler avec du texte -- mais je crois me rappeler que vous pourriez utiliser gauche et droite sur elle?

Philippe

153
répondu Philip Kelley 2009-06-22 18:13:15

la manière la plus simple est....

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))
91
répondu Mptje 2012-05-29 21:26:03

si vous utilisez Sqlserver 2005 ou plus, l'utilisation de la fonction REVERSE est souvent préjudiciable aux performances, le code ci-dessous est plus efficace.

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
40
répondu Binoj Antony 2015-12-14 07:24:40
DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'

SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt
13
répondu Shivendra 2016-03-03 09:53:33

cela a très bien fonctionné pour moi.

REVERSE(SUBSTRING(REVERSE([field]), CHARINDEX(REVERSE('[expr]'), REVERSE([field])) + DATALENGTH('[expr]'), DATALENGTH([field])))
7
répondu Karthik D V 2014-05-01 15:13:43

question ancienne mais toujours valable, donc heres ce que j'ai créé à partir des informations fournies par d'autres ici.

create function fnLastIndexOf(@text varChar(max),@char varchar(1))
returns int
as
begin
return len(@text) - charindex(@char, reverse(@text)) -1
end
6
répondu john 2012-06-25 16:13:20
REVERSE(SUBSTRING(REVERSE(ap_description),CHARINDEX('.',REVERSE(ap_description)),len(ap_description)))  

ont mieux fonctionné pour moi

6
répondu mark brito 2013-02-19 17:33:55

Hmm, je sais que c'est un vieux fil, mais une table de comptage pourrait faire cela dans SQL2000 (ou toute autre base de données):

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

un tableau de pointage est juste un tableau de nombres croissants.

le substring(@str, _Tally.n, 1) = @delim obtient la position de chaque délimiteur, puis vous obtenez juste la position maximale dans cet ensemble.

les tables de comptage sont géniales. Si vous ne les avez pas utilisés auparavant, il y a un bon article sur SQL Server Central (Libre reg, ou tout simplement utiliser de Bug Me Not ( http://www.bugmenot.com/view/sqlservercentral.com )).

*EDIT: Supprimé n <= LEN(TEXT_FIELD) , que vous ne pouvez pas utiliser LEN() sur le type de TEXTE. Aussi longtemps que le substring(...) = @delim reste bien que le résultat est encore correct.

4
répondu Chris 2010-09-15 04:33:06

inversez votre chaîne et votre substrat, puis cherchez la première occurrence.

2
répondu A-K 2009-06-22 18:04:10

je me rends compte que c'est une question vieille de plusieurs années, mais...

Sur Access 2010 , vous pouvez utiliser InStrRev() pour ce faire. Espérons que cette aide.

2
répondu Dan 2015-12-14 09:26:29

je sais que ce sera inefficace, mais avez-vous envisagé de lancer le champ text sur varchar pour que vous puissiez utiliser la solution fournie par le site que vous avez trouvé? Je sais que cette solution créerait des problèmes car vous pourriez potentiellement tronquer le document si la longueur dans le champ text dépassait la longueur de votre varchar (sans mentionner qu'il ne serait pas très performant).

puisque vos données sont dans un champ text (et vous utilisez SQL Server 2000) vos options sont limitées.

1
répondu Andrew Hare 2009-06-21 23:20:30

si vous voulez obtenir l'index du dernier espace dans une chaîne de mots, vous pouvez utiliser cette expression DROIT de(nom, prénom, (CHARINDEX(' ',l'INVERSE de(nom),0)) pour revenir le dernier mot dans la chaîne. Ceci est utile si vous souhaitez analyser le prénom d'un nom complet qui comprend initiales, par la première et /ou deuxième prénom.

0
répondu Justin Stephens 2013-01-07 17:34:28

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

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

N'ont pas testé, il pourrait être éteint par un en raison de l'indice zéro, mais fonctionne dans SUBSTRING fonction en coupant de @indexOf caractères à la fin de votre chaîne de caractères

SUBSTRING([MyField], 0, @LastIndexOf)

0
répondu Roan 2014-02-19 19:55:44

j'avais besoin de trouver la nième position d'un antislash dans un chemin de dossier. Voici ma solution.

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

Voici mes cas de test qui réussissent

SELECT dbo.GetLastIndexOf('f','1234567893456789\', 1) as indexOf -- expect 0 (no instances)
SELECT dbo.GetLastIndexOf('\','1234567893456789\', 1) as indexOf -- expect 20
SELECT dbo.GetLastIndexOf('\','1234567893456789\', 2) as indexOf -- expect 10
SELECT dbo.GetLastIndexOf('\','1234893456789\', 3) as indexOf -- expect 5
0
répondu fiat 2015-06-18 01:40:45

pour obtenir la partie avant la dernière occurrence du délimiteur (ne fonctionne que pour NVARCHAR en raison de DATALENGTH usage):

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

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

SELECT SUBSTRING(@Fullstring, 1, DATALENGTH(@Fullstring)/2 - CHARINDEX(@Delimiter, REVERSE(@Fullstring)));
0
répondu Hans M 2015-09-01 15:10:04

certaines des autres réponses renvoient une chaîne de caractères réelle alors que j'avais plus besoin de connaître l'index réel int. Et les réponses qui le font semblent compliquer les choses. En utilisant certaines des autres réponses comme source d'inspiration, j'ai fait ce qui suit...

tout d'abord, j'ai créé une fonction:

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

alors, dans votre requête vous pouvez simplement faire ceci:

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

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

ci-dessus doit retourner 23 (le dernier index de ':')

Espérons que cela fait un peu plus facile pour quelqu'un!

0
répondu Matt Goodwin 2015-12-03 01:29:19

This answer meets the requirements of the OP. en particulier, il permet à l'aiguille d'être plus d'un caractère simple et il ne génère pas d'erreur lorsque l'aiguille n'est pas trouvé dans une botte de foin. Il m'a semblé que le plus (tous?) des autres réponses ne gère pas ces cas limites. En outre, j'ai ajouté l'argument "position de départ" fourni par la fonction CharIndex du serveur MS SQL natif. J'ai essayé de reproduire exactement les spécifications de CharIndex sauf de traiter de droite à gauche au lieu de de gauche à droite. eg je renvoie null si l'aiguille ou la botte de foin est null et je renvoie zéro si l'aiguille n'est pas trouvée dans la botte de foin. Une chose que je ne pouvais pas contourner est qu'avec la fonction intégrée le troisième paramètre est optionnel. Avec les fonctions SQL Server définies par l'utilisateur, tous les paramètres doivent être fournis dans l'appel à moins que la fonction ne soit appelée en utilisant "EXEC" . Alors que le troisième paramètre doit être inclus dans la liste des paramètres, vous pouvez fournir le mot clé "default" comme un placeholder pour cela sans avoir à donner une valeur (voir les exemples ci-dessous). Puisqu'il est plus facile de supprimer le troisième paramètre de cette fonction s'il n'est pas désiré que de l'ajouter si nécessaire, je l'ai inclus ici comme point de départ.

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
0
répondu Ted Cohen 2015-12-06 20:22:32

je suis tombé sur ce fil en cherchant une solution à mon problème similaire qui avait la même exigence, mais était pour un type différent de base de données qui manquait également la fonction REVERSE .

dans mon cas, c'était pour une base de données OpenEdge (Progress) , qui a une syntaxe légèrement différente. Cela a rendu la fonction INSTR disponible pour moi que la plupart des bases de données dactylographiées Oracle offrent .

alors j'ai trouvé le code suivant:

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

Cependant, pour ma situation spécifique (étant la base de données OpenEdge (Progress) ) cela n'a pas donné le comportement désiré parce que remplacer le caractère par un char vide donnait la même longueur que la chaîne originale. Cela n'a pas beaucoup de sens pour moi, mais j'ai pu contourner le problème avec le code ci-dessous:

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

maintenant je comprends que ce code ne résoudra pas le problème pour T-SQL parce qu'il n'y a pas d'alternative à la fonction INSTR qui offre la propriété Occurence .

juste pour être complet, j'ajouterai le code nécessaire pour créer cette fonction scalaire afin qu'elle puisse être utilisée de la même manière que je l'ai fait dans les exemples ci-dessus.

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

pour éviter l'évidence, lorsque la fonction REVERSE est disponible vous n'avez pas besoin de créer cette fonction scalaire et vous pouvez juste obtenir le résultat requis comme ceci:

SELECT
  LEN(foo.filepath) - CHARINDEX('/', REVERSE(foo.filepath))+1 AS LastIndexOfSlash 
FROM foo
0
répondu Oceans 2017-09-13 10:17:58

ce code fonctionne même si la chaîne contient plus d'un caractère.

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
0
répondu Dmitry Kovganov 2018-08-23 10:26:16