获取SQL Server中某些字符之前和之后的所有内容

I got the following entry in my database:

我在我的数据库中输入了以下条目：

images/test.jpg

I want to trim the entry so I get: test

我想修剪条目所以我得到：测试

So basically, I want everything after / and before .

所以基本上，我想要之前/之前的一切。

How can I solve it?

我该如何解决？

9 个解决方案

#1

If you want to get this out of your table using SQL, take a look at the following functions that will help you: SUBSTRING and CHARINDEX. You can use those to trim your entries.

如果您想使用SQL将其从表中删除，请查看以下有助于您的函数：SUBSTRING和CHARINDEX。您可以使用它们来修剪条目。

A possible query will look like this (where col is the name of the column that contains your image directories:

可能的查询将如下所示（其中col是包含图像目录的列的名称：

SELECT SUBSTRING(col, LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) + 1, LEN(col) - LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) - LEN(SUBSTRING(col, CHARINDEX ('.', col), LEN(col))));

Bit of an ugly beast. It also depends on the standard format of 'dir/name.ext'.

有点丑陋的野兽。它还取决于'dir / name.ext'的标准格式。

Edit:
This one (inspired by praveen) is more generic and deals with extensions of different length:

编辑：这个（灵感来自praveen）更通用，处理不同长度的扩展：

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('/', col))) + 1, LEN(col) - LEN(LEFT(col, CHARINDEX ('/', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('.', col))) - 1);

#2

use the following function

使用以下功能

left(@test, charindex('/', @test) - 2)

#3

Before

之前

SELECT SUBSTRING(ParentBGBU,0,CHARINDEX('/',ParentBGBU,0)) FROM dbo.tblHCMMaster;

After

后

SELECT SUBSTRING(ParentBGBU,CHARINDEX('-',ParentBGBU)+1,LEN(ParentBGBU)) FROM dbo.tblHCMMaster

#4

 declare @T table
  (
  Col varchar(20)
  )



  insert into @T 
  Select 'images/test1.jpg'
  union all
  Select 'images/test2.png'
  union all
  Select 'images/test3.jpg'
  union all
  Select 'images/test4.jpeg'
  union all
  Select 'images/test5.jpeg'

 Select substring( LEFT(Col,charindex('.',Col)-1),charindex('/',Col)+1,len(LEFT(Col,charindex('.',Col)-1))-1 )
from @T

#5

I just did this in one of my reports and it was very simple.

我刚刚在我的一份报告中做到了这一点，这非常简单。

Try this:

尝试这个：

=MID(Fields!.Value,8,4)

= MID（领域！.value的，8,4）

Note: This worked for me because the value I was trying to get was a constant not sure it what you are trying to get is a constant as well.

注意：这对我有用，因为我试图得到的值是一个常数，不确定它你想要获得的是一个常数。

#6

I have made a method which is much more general :

我做了一个更通用的方法：

so :

所以：

DECLARE @a NVARCHAR(MAX)='images/test.jpg';


 --Touch here
DECLARE @keysValueToSearch NVARCHAR(4000) = '/'
DECLARE @untilThisCharAppears NVARCHAR(4000) = '.'
DECLARE @keysValueToSearchPattern NVARCHAR(4000) = '%' + @keysValueToSearch + '%'


 --Nothing to touch here     
SELECT SUBSTRING(
           @a,
           PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch),
           CHARINDEX(
               @untilThisCharAppears,
               @a,
               PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch)
           ) -(PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch))
       )

#7

I know this has been a while.. but here is an idea

我知道这已经有一段时间..但这是一个想法

declare @test varchar(25) = 'images/test.jpg'

select
 @test as column_name
 , parsename(replace(@test,'/','.'),1) as jpg
 ,parsename(replace(@test,'/','.'),2) as test
  ,parsename(replace(@test,'/','.'),3) as images

#8

Below query gives you data before '-' Ex- W12345A-4S

下面的查询为您提供' - 'Ex-W12345A-4S之前的数据

SELECT SUBSTRING(Column_Name,0, CHARINDEX('-',Column_Name)) as 'new_name' from [abc].

SELECT SUBSTRING（Column_Name，0，CHARINDEX（' - '，Column_Name））为[abc]中的'new_name'。

Output - W12345A

输出 - W12345A

#9

I found Royi Namir's answer useful but expanded upon it to create it as a function. I renamed the variables to what made sense to me but you can translate them back easily enough, if desired.

我发现Royi Namir的答案很有用，但在其上进行了扩展，以创建它作为一个功能。我将这些变量重命名为对我来说有意义的变量，但如果需要，你可以很容易地将它们翻译回来。

Also, the code in Royi's answer already handled the case where the character being searched from does not exist (it starts from the beginning of the string), but I wanted to also handle cases where the character that is being searched to does not exist.

此外，Royi的答案中的代码已经处理了从中搜索的字符不存在的情况（它从字符串的开头开始），但我还想处理不存在正在搜索的字符的情况。

In that case it acts in a similar manner by starting from the searched from character and returning the rest of the characters to the end of the string.

在这种情况下，它以类似的方式起作用，从搜索的字符开始并将其余字符返回到字符串的末尾。

CREATE FUNCTION [dbo].[getValueBetweenTwoStrings](@inputString 
NVARCHAR(4000), @stringToSearchFrom NVARCHAR(4000), @stringToSearchTo 
NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN      
DECLARE @retVal NVARCHAR(4000)
DECLARE @stringToSearchFromSearchPattern NVARCHAR(4000) = '%' + 
@stringToSearchFrom + '%'

SELECT @retVal = SUBSTRING (
       @inputString,
       PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom),
       (CASE
            CHARINDEX(
                @stringToSearchTo,
                @inputString,
                PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
        WHEN
            0
        THEN
            LEN(@inputString) + 1
        ELSE
            CHARINDEX(
                @stringToSearchTo,
                @inputString,
                PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
        END) - (PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
   )
RETURN @retVal
END

Usage:

用法：

SELECT dbo.getValueBetweenTwoStrings('images/test.jpg','/','.') AS MyResult