SQL Server 中有 LastIndexOf 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39002025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a LastIndexOf in SQL Server?
提问by AngryHacker
I am trying to parse out a value from a string that involves getting the lastindexof a string. Currently, I am doing a horrible hack that involves reversing a string:
我试图从涉及获取 string 的最后一个索引的string 中解析出一个值。目前，我正在做一个可怕的 hack，涉及反转字符串：
SELECT REVERSE(SUBSTRING(REVERSE(DB_NAME()), 1,
CHARINDEX('_', REVERSE(DB_NAME()), 1) - 1))
To me this code is nearly unreadable. I just upgraded to SQL Server 2016 and I hoping there is a better way. Is there?
对我来说，这段代码几乎不可读。我刚刚升级到 SQL Server 2016，希望有更好的方法。在那儿？
回答by Gordon Linoff
If you want everything after the last _, then use:
如果您想要 last 之后的所有内容_，请使用：
select right(db_name(), charindex('_', reverse(db_name()) + '_') - 1)
If you want everything before, then use left():
如果您之前想要所有内容，请使用left()：
select left(db_name(), len(db_name()) - charindex('_', reverse(db_name()) + '_'))
回答by user2771704
Wrote 2 functions, 1 to return LastIndexOffor the selected character.
编写了 2 个函数，1 个返回所选字符的LastIndexOf。
CREATE FUNCTION dbo.LastIndexOf(@source nvarchar(80), @pattern char)
RETURNS int
BEGIN
RETURN (LEN(@source)) - CHARINDEX(@pattern, REVERSE(@source))
END;
GO
and 1 to return a string before this LastIndexOf. Maybe it will be useful to someone.
和 1 返回此 LastIndexOf 之前的字符串。也许它会对某人有用。
CREATE FUNCTION dbo.StringBeforeLastIndex(@source nvarchar(80), @pattern char)
RETURNS nvarchar(80)
BEGIN
DECLARE @lastIndex int
SET @lastIndex = (LEN(@source)) - CHARINDEX(@pattern, REVERSE(@source))
RETURN SUBSTRING(@source, 0, @lastindex + 1)
-- +1 because index starts at 0, but length at 1, so to get up to 11th index, we need LENGTH 11+1=12
END;
GO
回答by Christoph
CREATE FUNCTION dbo.LastIndexOf(@text NTEXT, @delimiter NTEXT)
RETURNS INT
AS
BEGIN
IF (@text IS NULL) RETURN NULL;
IF (@delimiter IS NULL) RETURN NULL;
DECLARE @Text2 AS NVARCHAR(MAX) = @text;
DECLARE @Delimiter2 AS NVARCHAR(MAX) = @delimiter;
DECLARE @Index AS INT = CHARINDEX(REVERSE(@Delimiter2), REVERSE(@Text2));
IF (@Index < 1) RETURN 0;
DECLARE @ContentLength AS INT = (LEN('|' + @Text2 + '|') - 2);
DECLARE @DelimiterLength AS INT = (LEN('|' + @Delimiter2 + '|') - 2);
DECLARE @Result AS INT = (@ContentLength - @Index - @DelimiterLength + 2);
RETURN @Result;
END
- Allows for multi-character delimiters like ", " (comma space).
- Returns 0 if the delimiter is not found.
- Takes a NTEXT for comfort reasons as NVARCHAR(MAX)s are implicitely cast into NTEXT but not vice-versa.
- Handles delimiters with leading or tailing space correctly!
- 允许使用多字符分隔符，如“,”（逗号空格）。
- 如果未找到分隔符，则返回 0。
- 出于舒适的原因采用 NTEXT，因为 NVARCHAR(MAX) 被隐式转换为 NTEXT，反之亦然。
- 正确处理带有前导或尾随空格的分隔符！
回答by TheGameiswar
Once you have one of the split strings from here,you can do it in a set based way like this..
一旦您从这里获得了一个拆分字符串，您就可以像这样以基于集合的方式进行操作。
declare @string varchar(max)
set @string='C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf'
;with cte
as
(select *,row_number() over (order by (select null)) as rownum
from [dbo].[SplitStrings_Numbers](@string,'\')
)
select top 1 item from cte order by rownum desc
**Output:**
AdventureWorks_Data.mdf
回答by Juan Carlos Oropeza
No, SQL server doesnt have LastIndexOf.
不，SQL 服务器没有 LastIndexOf。
This are the available string functions
这是可用的字符串函数
But you can always can create your own function
但是你总是可以创建自己的函数
CREATE FUNCTION dbo.LastIndexOf(@source text, @pattern char)
RETURNS
AS
BEGIN
DECLARE @ret text;
SELECT into @ret
REVERSE(SUBSTRING(REVERSE(@source), 1,
CHARINDEX(@pattern, REVERSE(@source), 1) - 1))
RETURN @ret;
END;
GO
回答by Oceans
I came across this thread while searching for a solution to my similar problem which had the exact same requirement but was for a different kind of database that was lacking the REVERSEfunction.
我在寻找类似问题的解决方案时遇到了这个线程，该问题具有完全相同的要求，但适用于缺乏该REVERSE功能的不同类型的数据库。
In my case this was for a OpenEdge (Progress)database, which has a slightly different syntax. This made the INSTRfunction available to me that most Oracle typed databases offer.
就我而言，这是针对OpenEdge (Progress)数据库，它的语法略有不同。这使INSTR我可以使用大多数 Oracle 类型数据库提供的功能。
So I came up with the following code:
所以我想出了以下代码：
SELECT
INSTR(foo.filepath, '/',1, LENGTH(foo.filepath) - LENGTH( REPLACE( foo.filepath, '/', ''))) AS IndexOfLastSlash
FROM foo
However, for my specific situation (being the OpenEdge (Progress)database) this did not result into the desired behaviour because replacing the character with an empty char gave the same length as the original string. This doesn't make much sense to me but I was able to bypass the problem with the code below:
但是，对于我的特定情况（作为OpenEdge（进度）数据库），这并没有导致所需的行为，因为用空字符替换字符的长度与原始字符串相同。这对我来说没有多大意义，但我能够用下面的代码绕过这个问题：
SELECT
INSTR(foo.filepath, '/',1, LENGTH( REPLACE( foo.filepath, '/', 'XX')) - LENGTH(foo.filepath)) AS IndexOfLastSlash
FROM foo
Now I understand that this code won't solve the problem for T-SQLbecause there is no alternative to the INSTRfunction that offers the Occurenceproperty.
现在我明白这段代码不能解决T-SQL的问题，因为INSTR提供该Occurence属性的函数别无选择。
Just to be thorough I'll add the code needed to create this scalar function so it can be used the same way like I did in the above examples. And will do exactly what the OP wanted, serve as a LastIndexOfmethod for SQL Server.
只是为了彻底，我将添加创建此标量函数所需的代码，以便可以像在上面的示例中一样使用它。并且将完全按照 OP 的要求，作为SQL Server的LastIndexOf方法。
-- 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
To avoid the obvious, when the REVERSEfunction is available you do not need to create this scalar function and you can just get the required result like this:
为避免显而易见的情况，当REVERSE函数可用时，您无需创建此标量函数，您只需获得所需的结果，如下所示：
SELECT
LEN(foo.filepath) - CHARINDEX('\', REVERSE(foo.filepath))+1 AS LastIndexOfSlash
FROM foo
回答by Amarnath DK
Try this.
尝试这个。
drop table #temp
declare @brokername1 nvarchar(max)='indiabullssecurities,canmoney,indianivesh,acumencapitalmarket,sharekhan,edelweisscapital';
Create Table #temp
(
ID int identity(1,1) not null,
value varchar(100) not null
)
INSERT INTO #temp(value) SELECT value from STRING_SPLIT(@brokername1,',')
declare @id int;
set @id=(select max(id) from #temp)
--print @id
declare @results varchar(500)
select @results = coalesce(@results + ',', '') + convert(varchar(12),value)
from #temp where id<@id
order by id
print @results
