Case-insensitive REPLACE() in SQL Server 2000

Question

I have a field that contains strings such as 'Blah-OVER', 'Blah-OveR', etc. and want to select them without the 'over's. This only catches the first case (so to speak) and not the others:

SELECT field as "before", REPLACE(field, 'OVER', '') as "after"

How do I just get them all to say 'Blah-' (preserving the case of what's left) without attempting to cover every case combination with another nested REPLACE function?

Answer 1

Use a case insensitive collation:

SELECT field as "before", REPLACE(field COLLATE SQL_Latin1_General_Cp1_CI_AI
, 'OVER', '') as "after"

See COLLATE for list of collation names so you choose the one appropiate for your data.

Update

Ok, so I missed your actual request (change case of input, not find case-insensitive). The proper solution is... not to change the input but use an adequate collation for your data. If the data must be displayed in a specific format, use display options in the client, eg. CSS text-transform:uppercase, not in the server SELECT.

There isn't any built-in SQL function to do this transformation in-place, but is trivial to build a CLR function that uses RegEx. (On SQL 2005, not on SQL 2000... doh, I need more coffe).