How about a sample table, sample data and expected final data state?

Since using actual words, what about Full Text Search?

LIKEs are tough on the optimizer, as are all those ORs.

1) TRY_CONVERT could help

2) SSIS has a Data Profiling tool that could help

This thing is a beast. May I ask why you were the one tasked with tuning it? What are your qualifications - what makes your company think you will be...

There are umpteen things at play here and given the way you phrased the OP and the blatantly obvious issues with the query itself I will state right up front...

1) You just cannot hit the same table over and over and be successful.

2) CASE is your friend!!! Learn to use it!

3) All of the hits...

1) I still haven't seen anything on the server configuration.

2) There are numerous tables that have nothing but a clustered index. Some of those clustered indexes are pretty...

1) How can we possibly help you without table definitions (including existing indexes and keys)? 

2) Have you examined file IO stalls when this is running?

3) Have...