Regex is sexy. It's also a time-pig as you can get to invest a lot of time in an otherwise trivial task. I've gotten lost trying to code some really beautiful things in regex.
That said, why not try this? Read all lines of the file into a string array, then do something like this:
string[] lines = File.ReadAllLines("yourfile");
var mappingLines = from l in lines
where l.Contains(" as ")
select l;
foreach (var line in mappingLines)
{
string[] map = line.Split(" as ".ToCharArray());
}
The first bit filters you down to only the lines with " as " in them, giving you your maps. The second bit is just a simple string split.
You might need to work a little on the map after the split to pull out the CASE WHEN, but that would get you started. I'd need more info on that text pattern to help more.
Cheers.