Optimize Query

I need some help with optimizing this qry... The table tbl2, in the field dfrsloanno, has values such as 100024PRP and 10024.. ie some records are with the letters PRP at the end, and some records are without. Hence when I join ths tbl2 with tbl1 table, I have to join on both the occureences, ie look for the recs with the PRP and the ones without it too.

My qry howver, runs for a long time, ie 22 seconds, when I use the one below.

select dfrsloanno, a.* from tbl1 a
left join tbl2 comm
on (convert(varchar,a.acct#))=SubString(COMM.[dfrsloanno], 0, PatIndex('%[A-Z,a-z]%', COMM.[dfrsloanno]))

or ltrim(rtrim(COMM.[dfrsloanno])) =ltrim(rtrim(convert(varchar,a.acct#)))

However, when I take out the or criteria, it runs for only 2 seconds .. The reason I have the or crtieria is to meet both the scenarios ie 10024PRP and 10024... Hence I need the OR criteria. But I foudn that the culprit of the slowness in the qry is the OR condition...

Pl advise, how can I optimze this, and keep the OR condition too?

When you wrap various functions around column values, you negate the possibility of using any indexing on that column and the entire table must be scanned.

Is it possible to add a computed column (that could be indexed) that would be without the 'PRP'?

|||This worked very well. I did jsut like u suggested. On my tbl2, I put case statements, to tae into account both the PRP and without PRP. Then this qry returned results in 3 secs only.That is what I needed. Thanks