如何使用SUBSTRING使用2个不同的分隔符提取数据？

Dealing with a table that has a column that is a string. Here are some examples of what the strings look like:

处理具有字符串列的表。以下是字符串外观的一些示例：

 Fee Prorated 68%  -  $1.00  x 76
 Fee - Prorated 50% ($1.10 x 292)
 Fee - Prorated 50% ($1.00 x 242)
 Fee - Prorated 13% ($1.00 x 39)
 Prorated  Fee 45.16%  $1.00 x 256
 Fee - Prorated 26% ($1.00 x 56)
 Fee- Prorated 51.6%  $1.00 x 66
 Fee - Prorated 94% ($1.15 x 48)
 Fee - Prorated 52% ($1.10 x 120)
 Fee - Prorated 10% ($1.25 x 304)
 Fee - Prorated 10% ($1.25 x 304)
 Fees - prorated 46.67% ($1.50 x 230)
 Fees - prorated 23% ($1.25 x 989)
 Fees - prorated 87% ($1.25 x 348)
 Fees - prorated 48% ($1.25 x 210)
 Fees ($1.50 x 64) Prorated 30%
 Fees - prorated 30% ($1.50 x 51)
 Fees ($1.25 x 341) - Prorated 71%
 Fees - Prorated 58% ($1.50 x 196)
 Fees - Prorated 10% ($1.25 x 224)
 Fees - Prorated 61%($1.50 x 50)

I need to get a substring that is just the percentage so I can convert it to a decimal and then multiply by that amount.

我需要得到一个只是百分比的子字符串，所以我可以将它转换为小数，然后乘以该数量。

I am at a loss of how to do this except to try and use the % as a delimited and grabbing everything to the left of it until it gets to a space character - problem is I have no idea how do to it

我不知道如何做到这一点，除了尝试使用％作为分隔并抓住它左边的一切，直到它到达一个空格字符 - 问题是我不知道怎么做

3 个解决方案

#1

using charindex(), reverse(), and left() to determine the parameters for substring():

使用charindex（），reverse（）和left（）来确定substring（）的参数：

select 
    col
  , substring(
      col
    , charindex('%',col) - (charindex(' ',reverse(left(col,charindex('%',col)))+' ')-2)
    ,(charindex(' ',reverse(left(col,charindex('%',col)))+' ')-2)
    ) as Prorated
from t
where charindex('%',col)>0

rextester demo: http://rextester.com/GKVB60235

rextester演示：http：//rextester.com/GKVB60235

returns:

收益：

+--------------------------------------+----------+
|                 col                  | Prorated |
+--------------------------------------+----------+
| Fee Prorated 68%  -  $1.00  x 76     | 68       |
| Fee - Prorated 50% ($1.10 x 292)     | 50       |
| Fee - Prorated 50% ($1.00 x 242)     | 50       |
| Fee - Prorated 13% ($1.00 x 39)      | 13       |
| Prorated  Fee 45.16%  $1.00 x 256    | 45.16    |
| Fee - Prorated 26% ($1.00 x 56)      | 26       |
| Fee- Prorated 51.6%  $1.00 x 66      | 51.6     |
| Fee - Prorated 94% ($1.15 x 48)      | 94       |
| Fee - Prorated 52% ($1.10 x 120)     | 52       |
| Fee - Prorated 10% ($1.25 x 304)     | 10       |
| Fee - Prorated 10% ($1.25 x 304)     | 10       |
| Fees - prorated 46.67% ($1.50 x 230) | 46.67    |
| Fees - prorated 23% ($1.25 x 989)    | 23       |
| Fees - prorated 87% ($1.25 x 348)    | 87       |
| Fees - prorated 48% ($1.25 x 210)    | 48       |
| Fees ($1.50 x 64) Prorated 30%       | 30       |
| Fees - prorated 30% ($1.50 x 51)     | 30       |
| Fees ($1.25 x 341) - Prorated 71%    | 71       |
| Fees - Prorated 58% ($1.50 x 196)    | 58       |
| Fees - Prorated 10% ($1.25 x 224)    | 10       |
| Fees - Prorated 61%($1.50 x 50)      | 61       |
+--------------------------------------+----------+

#2

Fetching data from between strings and deriving your processing on it is riskier and not beneficial in long run. So I would suggest you to find how is the % value getting loaded in the column and see if you can have a separate column to load it, along side your table.

从字符串之间获取数据并从中获取处理数据风险较大，从长远来看并不是有益的。所以我建议你找一下如何在列中加载％值，看看你是否可以在表的旁边加载一个单独的列。

If it is not at all possible, and also if string pattern is not fixed, then you can do it with combination of charindex,substring and reverse.

如果它根本不可能，并且如果字符串模式没有修复，那么你可以使用charindex，substring和reverse的组合来完成它。

I would suggest you to check the regex approach also as mentioned in comments, as I am afraid that so many string function might slow down the query. So compare both approach and go with faster one.

我建议你检查注释中提到的正则表达式方法，因为我担心这么多字符串函数可能会减慢查询速度。因此，比较两种方法，并采用更快的方法。

Rextester Demo

Rextester演示

 SELECT 
 reverse
    (substring
        (reverse
            (substring
                (col,1,charindex('%',col,1))
            ),2,charindex(' ',reverse
                                (substring(col,1,charindex('%',col,1))
                                ),1
                         )-2
        )
    ) as percentage
FROM
  (SELECT 'Fee Prorated 68%  -  $1.00  x 76' AS col
   UNION ALL SELECT 'Fee - Prorated 50.0987% ($1.10 x 292)') t

Output

产量

percentage
----------
68
50.0987

#3

Another option is with a Parse/Split function.

另一种选择是使用Parse / Split功能。

Here we use a [SPACE] as the delimiter, but with a little twist to ensure capture. Before we pass the string into a the parser, we replace the [PERCENT] with [PERCENT]||[SPACE]

这里我们使用[SPACE]作为分隔符，但稍加扭曲以确保捕获。在我们将字符串传递给解析器之前，我们用[PERCENT] || [SPACE]替换[PERCENT]

Example

例

Select A.*
      ,Pcnt = replace(B.RetVal,'||','')
 From  YourTable A
 Cross Apply [dbo].[udf-Str-Parse-8K](replace(A.SomeCol,'%','%|| '),' ') B
 Where RetVal Like '%||'

Returns

SomeCol                              Pcnt
Fee Prorated 68% - $1.00 x 76       68%
Fee - Prorated 50% ($1.10 x 292)    50%
Fee - Prorated 50% ($1.00 x 242)    50%
Fee - Prorated 13% ($1.00 x 39)     13%
Prorated Fee 45.16% $1.00 x 256     45.16%
Fee - Prorated 26% ($1.00 x 56)     26%
Fee- Prorated 51.6% $1.00 x 66      51.6%
Fee - Prorated 94% ($1.15 x 48)     94%
Fee - Prorated 52% ($1.10 x 120)    52%
Fee - Prorated 10% ($1.25 x 304)    10%
Fee - Prorated 10% ($1.25 x 304)    10%
Fees - prorated 46.67% ($1.50 x 230)46.67%
Fees - prorated 23% ($1.25 x 989)   23%
Fees - prorated 87% ($1.25 x 348)   87%
Fees - prorated 48% ($1.25 x 210)   48%
Fees ($1.50 x 64) Prorated 30%      30%
Fees - prorated 30% ($1.50 x 51)    30%
Fees ($1.25 x 341) - Prorated 71%   71%
Fees - Prorated 58% ($1.50 x 196)   58%
Fees - Prorated 10% ($1.25 x 224)   10%
Fees - Prorated 61%($1.50 x 50)     61%

The UDF if Intersted

UDF如果已插入

CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')

#1