sql - 在子字符串中使用 charindex 来修剪字符串

Question

有一个列 name 我想从中创建一个新列。示例:

name
asd_abceur1mz_a
asd_fxasdrasdusd3mz_a
asd_abceur10yz_a
asd_fxasdrasdusd15yz_a

列的长度不固定，所以我假设我必须使用 charindex 来获得一个可以修剪的引用点。

我想要的:最后总是有 z_a，我需要将 z_a 的左侧部分放在单独的列中像这样:

nameNew
eur1m
usd3m
eur10y
usd15y

问题是数字(在本例中为 1、3、10、15)有一位或两位数字。我需要将信息从 name 提取到 nameNew。

之后我想让它更容易阅读并像这样输出:

eur_1m
usd_3m
eur_10y
usd_15y

我尝试结合使用 substring 和 charindex，但到目前为止没有成功。

SELECT  *
      , SUBSTRING(name, 1, ( CHARINDEX('z_a', NAME) - 1 )) AS nameNew
FROM    myTable

这是第一步，修剪字符串，第二步(使其更易于阅读)我不知道如何定位数字并放置 _。任何帮助，将不胜感激。使用 sql server 2012

编辑:

首先感谢您抽出时间提供解决方案。但是你的查询或多或少即使他们为 1 或 2 位数字工作也有同样的问题。考虑这种情况:

name
ab_dertEUR03EUR10YZ_A

如果 eur 在字符串中出现了两次，那么我该如何消除呢？很抱歉没有在我的原始帖子中包含这个，但我忘记了这种情况是可能的，现在这是一个问题。

编辑:

在此示例中测试您的查询: http://www.sqlfiddle.com/#!3/21610/1

请注意，最后可以是 1 位或 2 位数字和字母 y 或 m 的任意组合。

例如:ab_rtgtEUR03EUR2YZ_A、ab_rtgtEUR03EUR2mZ_A、ab_rtgtEUR03EUR20YZ_A、ab_rtgtEUR03EUR20mZ_A

一些测试值:('ex_CHFCHF01CHF10YZ_A'), ('ab_rtgtEUR03EUR2YZ_A'), ('RON_asdRON2MZ_A'),
('tg_USDUSD04USD5YZ_A');

我对您的查询的理解是它们执行与此类似的操作(或者至少它们应该执行)

ex_CHFCHF01CHF10YZ_A -> ex_CHFCHF01CHF10Y -> Y01FHC10FHCFHC -> Y01FHC -> CHF01Y -> CHF_01Y
RON_asdRON2MZ_A      ->  RON_asdRON2M     -> M2NORdsa_ron   -> M2NOR  -> RON2M  -> RON_2M

Answer 1

这适用于一个或两个数字:

stuff(case
        when name like '%[0-9][0-9]_z[_]a'
          then left(right(name, 9), 6)
        when name like '%[0-9]_z[_]a'
          then left(right(name, 8), 5)
      end, 4, 0, '_')