Grouping Codes by Prefix with Group Table

[Anonymous]

Hello All,

I have a list of gifts to our organization that come in with a Solicitation Code (CODE). I also have a requirement to group gifts by solicitation code using a CodeGroups Table. The issue I'm having is that the Mapping Table only uses the prefixes of the codes.

Gifts

Gift ID	Code
001	USAM1205
002	USAT0023
003	USAM1109
004	AT0987
005	USBR1976
006	USBRCM
007	USAM--DD
008	USAT--09
009	USBR----
010	USAMJACKS
011	USBRG17H

CodeGroups

Code	Group
AT	Web
USAM	Mail
USAT	Missions
USBR	Spanish
USBRCM	Spanish Mission

I need the result to be

Gift ID	Code	Group
001	USAM1205	Mail
002	USAT0023	Missions
003	USAM1109	Mail
004	AT0987	Web
005	USBR1976	Spanish
006	USBRCM	Spanish Mission
007	USAM--DD	Mail
008	USAT--09	Missions
009	USBR----	Spanish
010	USAMJACKS	Mail
011	USBRG17H	Spanish

Any one know of a solution for something like this where the prefix length can vary?

EDITED** Added gifts 007, 008, and 009

EDIT@* Added Gifts 010 and 011

[Anonymous]

I noticed that I have some codes that aren't mapping correctly due to a slightly different format. Such as "A00DB02" or "A02DB03" or "DAOB". These come in as "A00ERROR:MISSING CODE" or "DAERROR:MISSING CODE".

How do we get these to just show as other?