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...

I may not fully understand your requirement, but either:

(1) A CHECK constraint

or

(2) an index definition

can be created to do what you want to do.

For example, if you just want to make...

I'd flip the check around: check first for = 1 as 'True' else 'False'.  That way NULL will show as false, if a NULL happens to slip in there.

Use the...

You can use ctes, which will create virtual tables, but no actual physical tables:

;WITH TempA AS (
    SELECT
    InvoiceNo, Date,Type,Code
 ...

I would use STUFF for this, as most custom-fitted to do this task:

SELECT s_Result,
    STUFF(s_Result, 1, CASE s_credit_card_type 
     ...