I suggest removing ALL non-numeric values in the query itself rather than trying to go thru the data.  For example, by making this mod to the query:

...
...

No DDL provided, but my best guess is that one of these columns:

t1.invoicenum; t2.invoicenum

is integer and the other is not, and that one contains a value of 'VZ34-031'

Since they are "or" conditions, SQL should be able to stop evaluating at the "first" one that is true.  In theory, though, SQL could re-arrange the checks and make a...

I think this will match what you need, if I understand correctly:

SELECT ...

FROM dbo.baseTable bt

LEFT OUTER JOIN dbo.sentTable st ON st.sent_rowid = bt.rowid

WHERE ((@vendor IS NULL AND st.sent_rowid IS NULL)...

If you only want to replace leading and/or trailing commas, then this:

UPDATE tn
SET oppo_SCRMcompetitor = CASE WHEN LEFT(oppo_SCRMcompetitor_ca1, 1) = ',' 
    THEN STUFF(oppo_SCRMcompetitor_ca1,...

If you always want to replace every comma, you can do this:

UPDATE dbo.table_name

SET oppo_SCRMcompetitor = REPLACE(oppo_SCRMcompetitor, ',', '')

WHERE oppo_SCRMcompetitor LIKE '%,%'

That is SQL's method for handling insufficient space on a CAST.

The only safe method would be to use 11 chars. 10 max digits plus the prefix char (assuming you don't...

Definitely focus on the clustered index, first and foremost!

The best way to choose the keys is to look at the missing index stats and current index usage stats and decide...

If you just want to change the result column in the SELECT, then do this:

...,

CustID =

(CASE

WHEN custid like '%abc%' and company = 'abc'

then null

ELSE custid

END),

...

As to performance:

I figure SQL would have to partition the data twice to get the COUNT() and the AVG() from different queries, but I'd need to look at the query...

It's "> 2" :-).

I'm not sure how we'd be able to determine whether more than 2 rows are present with that style of query, in order to meet the stated requirements.  Then again,...

My primary goal is to meet the stated business requirements for the code, which I don't believe your code does.  You will list the "truePct" for even a single row,...

You will need a trigger if you have to reference other rows in the table.  A well-written trigger won't hurt your performance that much  assuming you have an index available...

Maybe this?:

SELECT  poll_id ,start_date,end_date,
candidate_name, sample_size,
(select avg(pp2.pct) from [president_polls] pp2 
    where pp2.state = pp.state and pp2.poll_id = pp.poll_id 
    having...