using charindex in a substring to trim a string

Question

There is a column name from which I want to use to make a new column. example:

name
asd_abceur1mz_a
asd_fxasdrasdusd3mz_a
asd_abceur10yz_a
asd_fxasdrasdusd15yz_a

The length of the column is not fixed so I assumed i have to use charindex to have a reference point from which I could trim.

What i want: at the end there is always z_a, and i need to place in a separate column the left part from z_a like this:

nameNew
eur1m
usd3m
eur10y
usd15y

The problem is that the number (in this example 1, 3, 10, 15) has 1 or two digits. I need to extract the information from name to nameNew.

After that i was thinking to make it easier to read and to output it like this:

eur_1m
usd_3m
eur_10y
usd_15y

I tried using a combination of substring and charindex, but so far without success.

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

This is for the first step, trimming the string, for the 2nd step (making it easier to read) I don't know how to target the digit and place an _. Any help would be appreciated. Using sql server 2012

edit:

First of all thank you for your time and solutions. But your queries more or less even if they are working for 1 or 2 digits have the same problem. Consider this situation:

name
ab_dertEUR03EUR10YZ_A

if eur is two times in the string, then how can I eliminate this? Sorry for not includding this in my original post but i forgot that situation is possible and now that's a problem.

edit:

test your queries here, on this example: http://www.sqlfiddle.com/#!3/21610/1

Please note that at the end it can be any combination of 1 or 2 digits and the letter y or m.

Ex: ab_rtgtEUR03EUR2YZ_A , ab_rtgtEUR03EUR2mZ_A, ab_rtgtEUR03EUR20YZ_A, ab_rtgtEUR03EUR20mZ_A

Some values for testing: ('ex_CHFCHF01CHF10YZ_A'), ('ab_rtgtEUR03EUR2YZ_A'), ('RON_asdRON2MZ_A'), ('tg_USDUSD04USD5YZ_A');

My understanding of your queries is that they perform something simillar to this (or at least they should)

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

Answer 1

This works for one or two digits:

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, '_')