在 SQL Server 中获取子字符串 [英] Get substring in SQL Server

问题描述

我想在 SQL Server 中从点分割的最后一个序列 (.) 中获取一个子字符串.

I want to get a substring in SQL Server from last sequence of a split on dot (.).

我有一列包含文件名，例如 hello.exe，我想找到与 Path.GetExtension("filename") 在 C# 中执行.

I have a column which contains file names such as hello.exe, and I want to find the extension of the file exactly as Path.GetExtension("filename") does in C#.

推荐答案

您可以使用 reverse 和 substring 和 charindex 获取您要查找的内容:

You can use reverse along with substring and charindex to get what you're looking for:

select
    reverse(substring(reverse(filename), 1, 
        charindex('.', reverse(filename))-1)) as FileExt
from
    mytable

即使您的文件中有多个 .(例如-hello.world.exe 将返回 exe)，这也是成立的.

This holds up, even if you have multiple . in your file (e.g.-hello.world.exe will return exe).

所以我在玩这个，这是另一种方式(只有一次调用reverse):

So I was playing around a bit with this, and this is another way (only one call to reverse):

select 
    SUBSTRING(filename, 
        LEN(filename)-(CHARINDEX('.', reverse(filename))-2), 8000) as FileExt
from
    mytable

这会在 25 秒内计算 10,000,000 行，而前一种方法需要 29 秒.

This calculates 10,000,000 rows in 25 seconds versus 29 seconds for the former method.

这篇关于在 SQL Server 中获取子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！