search for a string in a case-sensitive table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

search for a string in a case-sensitive table

Author

Topic

cckelly
Starting Member

9 Posts

Posted - 2010-04-23 : 09:55:46

On a case-sensitive SQL2005 server table that I cannot change, there are 5 columns that I must search for the occurrence of a text string.

1st-3rd columns are defined as varchar(15), not null
4th column is defined as "notes (text, null)"
5th column is defined as "prnotes2 (text, null)"

I tried this:
SELECT {list of columns} FROM tables {correctly joined}
WHERE (date_col >= {ts '2010-04-01 07:29:00'}) and
((UPPER(col1) LIKE '%POD%') or
(UPPER(col2) LIKE '%POD%') or
(UPPER(col3) LIKE '%POD%') or
(UPPER(notes) LIKE '%POD%') or
(UPPER(prnotes2) LIKE '%POD%'));

Here's the message I get:
Argument data type text is invalid for argument 1 of upper function.

I individually added the columns to the WHERE clause, and it doesn't like the notes line, or the prnotes2 line.

What do I need to do to have a data-row listed if any of the 5 columns have any "case" of p-o-d anywhere in the string

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 09:59:21

use collate

...
WHERE (date_col >= {ts '2010-04-01 07:29:00'}) and
(col1 COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%POD%') or
(col2 COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%POD%') or
(col3 COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%POD%') or
(notes COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%POD%') or
(prnotes2 COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%POD%');

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/