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.
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
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
la manière la plus simple est....
REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))
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
DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'
SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt
cela a très bien fonctionné pour moi.
REVERSE(SUBSTRING(REVERSE([field]), CHARINDEX(REVERSE('[expr]'), REVERSE([field])) + DATALENGTH('[expr]'), DATALENGTH([field])))
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
REVERSE(SUBSTRING(REVERSE(ap_description),CHARINDEX('.',REVERSE(ap_description)),len(ap_description)))
ont mieux fonctionné pour moi
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.
inversez votre chaîne et votre substrat, puis cherchez la première occurrence.
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.
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.
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.
@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)
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
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)));
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!
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
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
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