MS SQL question on ntext field - HELP!

yes, I think my database does have the case-sensitively set on.  I have a field with value "Catholic"
then if someone try to search for "catholic", nothing return

Looking at your query, I note that you have a '%' only at the right side of the matching string.  If this is the only place you will have the match wildcard, then for an NText field, you can use something like this (assumes a stored procedure for this example, without a wildcard):


WHERE Upper(SubString(Field1, 1, Len(@MyValue)) = Upper(@MyValue)

Missed a closing parentheses:

WHERE Upper(SubString(Field1, 1, Len(@MyValue))) = Upper(@MyValue)

ntext column cannot be altered, it must be moved.

create table TableX(T ntext collate Latin1_General_CS_AS not null)
GO

alter table TableX add TX ntext collate Latin1_General_CI_AS not null default ''

--Put a modified BLOB moving script here for T->TX
See http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20571153.html

alter table TableX drop column T
EXEC sp_rename 'TableX.[TX]', 'T', 'COLUMN'

You can't use the like statement with an ntext field I think.  I would suggest:

WHERE UPPER(cast(field1 as varchar(8000)) LIKE 'myValue%'

The UPPER/LOWER stuff is irrelevant I think.

Hope that helps.

Try changing:
WHERE UPPER(field1) LIKE 'myValue%'

To:
WHERE PatIndex('%myValue%', field1) > 0

Anthony

acperkins,
I have verified LIKE '%d' for 13 MB ntext string, is this behavior caused by the new SP3? Also notice MaxSterling's post, UPPER() cannot be used with ntext directly.

gab_chong;

Rather than going through the exercise of changing tables, you may be able to get by with specifying the desired case-sensitivity at "compare-time" (note the COLLATE clause):

----- SQL SCRIPT -------
CREATE TABLE WithNTEXT(MyText ntext)
GO

INSERT INTO WithNTEXT VALUES('abcd')
INSERT INTO WithNTEXT VALUES('ABCD')
GO

--My server is set up case-insensitive. Therefore, I get both rows...
SELECT MyText FROM WithNTEXT WHERE MyText LIKE 'abcd%'

--Explicitely compare case-insensitive
SELECT MyText FROM WithNTEXT WHERE MyText LIKE 'abcd%' COLLATE Latin1_General_CI_AS
GO

--Explicitely compare case-sensitive
SELECT MyText FROM WithNTEXT WHERE MyText LIKE 'abcd%' COLLATE Latin1_General_CS_AS
GO

DROP TABLE WithNTEXT
GO
----- END SQL SCRIPT ----

HTH,
TroyK, MCSD

TroyK,
the problem with COLLATE is that it is assumed to be nondeterministic. If you create an index on a computed column, for ntext it can be UPPER(SUBSTRING()) only.
If the user wants to get results of LIKE 'xyz%' faster (up to 450 nchars), he must add the computed column anyway. Regardless of the case-sensitivity of the original column collation.