SQL Server 2005:charindex 从末尾开始 [英] SQL Server 2005:charindex starting from the end

问题描述

我有一个字符串'some.file.name'，我想获取'some.file'.

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

为此，我需要找到最后一次出现的 '.'在一个字符串中.

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

我的解决方案是:

 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)

嗯，是不是太复杂了?我只是想在 where 子句中使用some.file".

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

有人有好主意吗?

推荐答案

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

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

如果是:反转字符串并使用普通的CHARINDEX搜索:

If so: reverse the string and search using the normal 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))

你会得到some.file"——直到最后一个."的字符.在原始文件名中.

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

直接在 SQL Server 中没有LASTCHARINDEX"或类似的东西.您可能会考虑在 SQL Server 2005 及更高版本中做一个很棒的 .NET 扩展库，并将其作为程序集部署到 SQL Server 中 - T-SQL 在字符串操作方面不是很强大，而 .NET 确实如此.

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 2005:charindex 从末尾开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！