views:

180

answers:

2

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?

A: 

I'm not familiar with SQL Server, but maybe it allows you to make use of regular expressions. These usually offer a case-insensitive mode (set via the i-flag).

Otherwise you could uppercase before the replace call, e.g.

SELECT field as "before", REPLACE(UPPER(field), 'OVER', '') as "after"
middus
It produces 'BLAH-'. So it changes the input string not only by replacing 'OVER'.
Lukasz Lysik
One site I came across suggested you needed some custom DLL installed to use regexes on 2000. Also, that returns all the 'blah's as capitalized--sorry, I should've specified that in the question, will edit.
Kev
+3  A: 

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

Remus Rusanu
This syntax is new to me! Thanks!
Kev