SQL Server 2005:charindex 从末尾开始

SQL Server 2005:charindex starting from the end

提问by Shuo

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?

有人有好主意吗？

回答by marc_s

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 确实如此。

回答by Tom H

This will also work:

这也将起作用：

DECLARE
     @test     VARCHAR(100)

SET @test = 'some.file.name'

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

回答by Gabriel Rosales

A very simple way is:

一个非常简单的方法是：

SELECT
    RIGHT(@str, CHARINDEX('.', REVERSE(@str)) - 1)

回答by Ryan H

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

回答by sheeba

Take one ')'

拿一个 '）'

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

回答by monsey11

Here is a shorter version

这是一个较短的版本

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

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