DECLARE @t TABLE
(
   Id INT,
   CS_text VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS,
   CI_text VARCHAR(30)
)

INSERT INTO @t
             SELECT 1, 'hello', 'world'
   UNION ALL SELECT 2, 'Hello', 'World'

SELECT *
   FROM @t
   WHERE CS_text = 'Hello'

SELECT *
   FROM @t
   WHERE CI_text COLLATE SQL_Latin1_General_CP1_CS_AS = 'World'

DECLARE @t TABLE
(
   txt VARCHAR(30)
)

INSERT INTO @T
             SELECT 'CQS0002509'
   UNION ALL SELECT 'CQS0002510'
   UNION ALL SELECT 'CQS0002511'
   UNION ALL SELECT 'cqs0002512'
   UNION ALL SELECT 'cqs0002513'
   UNION ALL SELECT 'cqs0002514'
   UNION ALL SELECT 'cqs0002515'
   UNION ALL SELECT 'CQS0002516'
   UNION ALL SELECT 'CQS0002517'
   UNION ALL SELECT 'CQS0002518'
   UNION ALL SELECT 'CQS0002519'
   UNION ALL SELECT 'CQS0002520'

UPDATE t SET txt = UPPER(txt)
   FROM @t t
   WHERE txt COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'

SELECT * FROM @t

UPDATE  sometable
      SET someColumn = REPLACE(someColumn,'cqs','CQS')
WHERE   somecolumn LIKE 'cqs%'

;with Tally (N) AS (SELECT TOP 10000 ROW_NUMBER() over (order by sc1.id) FROM Master.dbo.SysColumns sc1)
select 'cqs'+LEFT('00000'+CAST(N as varchar(10)),10) as keyno
into #testTable
from tally
create unique clustered index #pk_TestTable on #testTable ( keyno )


update #testTable
	set Keyno = UPPER(keyNo)
where keyNo like '%1%'

set statistics time on;

print '--Flo'
	UPDATE #testTable 
		SET keyno = UPPER(keyNo)
	FROM #testTable t
	WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'
set statistics time off;

-- reset
update #testTable
	set Keyno = lower(keyNo)
where keyNo not like '%1%'

set statistics time on;
print '--Bob'
	UPDATE  #testTable   
		SET keyno = REPLACE(keyNo,'cqs','CQS')
	WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'
	
	
set statistics time off;

drop table #testTable

UPDATE  sometable
      SET someColumn = REPLACE(someColumn,'cqs','CQS')
WHERE   somecolumn LIKE 'cqs%'

--- reset
update #testTable
   set Keyno = lower(keyNo)
where keyNo not like '%1%'
 
set statistics time on;
print '--Flo 2'
  UPDATE  #testTable  
       SET keyno = STUFF(keyNo, 1, 3, 'CQS')
  WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'
set statistics time off;

Sorry to butt in, Flo, but you're answering ALL the questions!!

By the way, my compliments on the Tally thread. It has been fascinating to follow. I haven't thought of any alternatives worth adding to the thread, but it has been thought provoking and educational all the same. I haven't seen such substantive debate going on in any forum outside SSC. Congratulations!