SQL Server 2005：从最后开始的charindex

I have a string 'some.file.name',I want to grab 'some.file'.

我有一个字符串'some.file.name'，我想抓住'some.file'。

To do that,I need to find the last occurrence of '.' in a string.

要做到这一点，我需要找到'。'的最后一次出现。在一个字符串中。

My solution is :

我的解决方案是：

 declare @someStr varchar(20)

 declare @reversedStr varchar(20)

 declare @index int

 set @someStr = '001.002.003'

 set @reversedStr = reverse(@someStr)

 set @index = len(@someStr) - charindex('.',@reversedStr)

 select left(@someStr,@index)

Well,isn't it too complicated?I was just intented to using 'some.file' in a where-clause.

好吧，是不是太复杂了？我只是想在where子句中使用'some.file'。

Anyone has a good idea?

谁有个好主意？

6 个解决方案

#1

What do you need to do with it?? Do you need to grab the characters after the last occurence of a given delimiter?

你需要做什么？在给定分隔符的最后一次出现后，您是否需要抓取字符？

If so: reverse the string and search using the normal CHARINDEX:

如果是这样：反转字符串并使用普通的CHARINDEX进行搜索：

declare @test varchar(100)
set @test = 'some.file.name'

declare @reversed varchar(100)
set @reversed = REVERSE(@test)

select 
    REVERSE(SUBSTRING(@reversed, CHARINDEX('.', @reversed)+1, 100))

You'll get back "some.file" - the characters up to the last "." in the original file name.

你会得到“some.file” - 直到最后一个“。”的字符。在原始文件名中。

There's no "LASTCHARINDEX" or anything like that in SQL Server directly. What you might consider doing in SQL Server 2005 and up is great a .NET extension library and deploy it as an assembly into SQL Server - T-SQL is not very strong with string manipulation, whereas .NET really is.

SQL Server中没有“LASTCHARINDEX”或类似内容。你可以考虑在SQL Server 2005及更高版本中做什么是一个伟大的.NET扩展库，并将其作为一个程序集部署到SQL Server中 - T-SQL在字符串操作方面不是很强大，而.NET确实如此。

#2

This will also work:

这也有效：

DECLARE
     @test     VARCHAR(100)

SET @test = 'some.file.name'

SELECT
     LEFT(@test, LEN(@test) - CHARINDEX('.', REVERSE(@test)))

#3

A very simple way is:

一个非常简单的方法是：

select right(@str,charindex('.',reverse(@str))-1)

选择正确（@ str，charindex（'。'，reverse（@str）） - 1）

#4

Take one ')'

拿一个 '）'

declare @test varchar(100)
set @test = 'some.file.name'
select left(@test,charindex('.',@test)+charindex('.',@test)-1)

#5

CREATE FUNCTION [dbo].[Instr] (
  -------------------------------------------------------------------------------------------------
  -- Name:     [dbo].[Instr]
  -- Purpose:  Find The Nth Value Within A String
  -------------------------------------------------------------------------------------------------
  -- Revisions:
  --   25-FEB-2011 - HESSR - Initial Revision
  -------------------------------------------------------------------------------------------------
  -- Parameters:
  --    1) @in_FindString - NVARCHAR(MAX) - INPUT - Input Find String
  --    2) @in_String - NVARCHAR(MAX) - INPUT - Input String
  --    3) @in_StartPos - SMALLINT - INPUT - Position In The String To Start Looking From
  --          (If Start Position Is Negative, Search Begins At The End Of The String)
  --          (Negative 1 Starts At End Position 1, Negative 3 Starts At End Position Minus 2)
  --    4) @in_Nth - SMALLINT - INPUT - Nth Occurrence To Find The Location For
  -------------------------------------------------------------------------------------------------
  -- Returns: SMALLINT - Position Of String Segment (Not Found = 0)
  -------------------------------------------------------------------------------------------------
  @in_FindString             NVARCHAR(MAX),
  @in_String                 NVARCHAR(MAX),
  @in_StartPos               SMALLINT            = NULL,
  @in_Nth                    SMALLINT            = NULL
  ) 
  RETURNS                    SMALLINT
AS
BEGIN

  DECLARE @loc_FindString NVARCHAR(MAX);
  DECLARE @loc_String NVARCHAR(MAX);
  DECLARE @loc_Position SMALLINT;
  DECLARE @loc_StartPos SMALLINT;
  DECLARE @loc_Nth SMALLINT;
  DECLARE @loc_Idx SMALLINT;
  DECLARE @loc_FindLength SMALLINT;
  DECLARE @loc_Length SMALLINT;

  SET @loc_FindString = @in_FindString;
  SET @loc_String = @in_String;

  SET @loc_Nth = ISNULL(ABS(@in_Nth), 1);
  SET @loc_FindLength = LEN(@loc_FindString+N'.') - 1;
  SET @loc_Length = LEN(@loc_String+N'.') - 1;

  SET @loc_StartPos = ISNULL(@in_StartPos, 1);
  SET @loc_Idx = 0;

  IF (@loc_StartPos = ABS(@loc_StartPos))
    BEGIN
      WHILE (@loc_Idx < @loc_Nth)
        BEGIN
          SET @loc_Position = CHARINDEX(@loc_FindString,@loc_String,@loc_StartPos);
          IF (@loc_Position > 0)
            SET @loc_StartPos = @loc_Position + @loc_FindLength
          ELSE
            SET @loc_Idx = @loc_Nth;
          SET @loc_Idx = @loc_Idx + 1;
        END;
    END
  ELSE
    BEGIN
      SET @loc_StartPos = ABS(@loc_StartPos);
      SET @loc_FindString = REVERSE(@in_FindString);
      SET @loc_String = REVERSE(@in_String);
      WHILE (@loc_Idx < @loc_Nth)
        BEGIN
          SET @loc_Position = CHARINDEX(@loc_FindString,@loc_String,@loc_StartPos);
          IF (@loc_Position > 0)
            SET @loc_StartPos = @loc_Position + @loc_FindLength
          ELSE
            SET @loc_Idx = @loc_Nth;
          SET @loc_Idx = @loc_Idx + 1;
        END;
      IF (@loc_Position > 0)
        SET @loc_Position = @loc_Length - @loc_Position + (1 - @loc_FindLength) + 1;
    END;

  RETURN (@loc_Position);

END;
GO

#6

Here is a shorter version

这是一个较短的版本

DECLARE @someStr varchar(20)
set @someStr = '001.002.003'

SELECT REVERSE(Substring(REVERSE(@someStr),CHARINDEX('.', REVERSE(@someStr))+1,20))

#1