0 - 2 of 2 tags for CASE with Substring

Hi - I fully understand that this is not the best way to organize data.  However, this is how the data already exists, and i want to run a query and coul duse some help!  I have field aa in TABLE AA, and if i take a substring of field aa it will match perfectly to field bb of TABLE BB.  and once matched, I get lots of wonderful information from TABLE BB.

Hi I am having an issue with below syntax:
When I run below query