根据与“val1”匹配的条件，如何将“val2”从一个格式化为“val1A-val2A~val1B-val2B~val1C-val2C”的字符串中提取?

In a certain SQL table I'm working on, there's a column that contains data formatted like:

在我正在处理的某个SQL表中，有一个列包含格式化的数据:

"year-text~year-text~year-text~year-text~year-text~year-text~year-text~" and so on and so forth.

“year-text~year-text~year-text~year-text~year-text~year-text~”等等。

(year is in 'yyyy' format)

(年份为“yyyyyy”格式)

(for example): "2012-10000~2013-5000~2014-500~2015-50000~2016-100~"

(例如):“2012 - 10000 ~ 2013 - 5000 ~ 2014 - 500 ~ 2015 - 50000 ~ 2016 - 100 ~”

How, using SQL might I extract, say, the value "50000" based on having the year, "2015"

如何使用SQL，比如，根据年份“2015”提取“50000”

Things to note/clarify:

注意事项/澄清:

The "-" and "~" characters can be trusted as delimiters. That is, they do not exist within any of the values or, of course, the years.
可以信任“-”和“~”字符作为分隔符。也就是说，它们不存在于任何值中，当然也不存在于年份。
No year exists without a value. In other words, if the value becomes blank, the year is stripped out, as well (In other words, the stored string will never have an "-" and a "~" right next to each other, such as 2016 in the string "2015-200~2016-~2014-1000", for example).
任何一年都是没有价值的。换句话说，如果值变为空白，那么年也将被剥离(换句话说，存储的字符串永远不会有一个“-”和一个“~”紧挨着，比如2016年的字符串“2015-200~2016-~2014-1000”)。
The years in the string may not be in chronological order from left to right.
字符串中的年份可能不是从左到右的时间顺序。
There could be virtually any number of years (each with a value) in the string or, indeed, none, at all. If no year/value pair exists for the column, the value becomes NULL
实际上，字符串中可能有任意数量的年份(每个年份都有一个值)，甚至根本没有年份。如果列中不存在年/值对，则该值为NULL
Please note that after each value for each year the character "~" is applied even if it is the last year/value pair. Any string value that is not NULL will therefore always end with a "~".
请注意，在每年的每个值之后，即使是最后一年/值对，也会应用“~”字符。因此，任何不是NULL的字符串值都将以“~”结束。

3 个解决方案

#1

Here is an option which uses SUBSTRING and CHARINDEX to get the job done:

下面是一个使用子字符串和CHARINDEX来完成任务的选项:

SUBSTRING(col,
          CHARINDEX('2015', col) + 5,
          CHARINDEX('~', col, CHARINDEX('2015', col) + 5) - (CHARINDEX('2015', col) + 5))

In the sample input

在样例输入

2012-10000~2013-5000~2014-500~2015-50000~2016-100~

CHARINDEX('2015', col) + 5 would start at the 5 in the number 50000 after the occurrence of 2015.

自2015年以来，该指数(“2015”、“col”)+ 5的起点为5，为50000。

The term

这个词

CHARINDEX('~', col, CHARINDEX('2015', col) + 5) - (CHARINDEX('2015', col) + 5)

yields the length of the number 50000, which in this case would be 5.

输出50000的长度，在本例中是5。

#2

Perhaps this can help

也许这可以帮助

With the aid of a parser and cross apply

借助解析器和交叉应用

Declare @String varchar(max) = '012-10000~2013-5000~2014-500~2015-50000~2016-100~'

Select A.*
      ,B.* 
 From [dbo].[udf-Str-Parse](@String,'~') A
 Cross Apply (Select Val1=max(IIF(Key_PS=1,Key_Value,NULL))
                    ,Val2=max(IIF(Key_PS=2,Key_Value,NULL)) 
               From [dbo].[udf-Str-Parse](A.Key_Value,'-')) B
 Where A.Key_Value<>''

Returns

Key_PS  Key_Value   Val1    Val2
1       012-10000   012     10000
2       2013-5000   2013    5000
3       2014-500    2014    500
4       2015-50000  2015    50000
5       2016-100    2016    100

My Parser if needed

如果需要我的解析器

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

#3

you can use substr and charindex

可以使用substr和charindex

select  substr( charindex( 'your_val', your_column) + length('your__val') +1, 4);

in your case

在你的情况中

select  substr( charindex( '2016', your_column) + length('2016') +1, 4);

#1