在 SQL Server 中获取子字符串

本文介绍了在 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.