?
Solved

SEARCH in TEXT field type in CASE SENSITIVE MS SQL SERVER 2000 Database

Posted on 2004-08-17
7
Medium Priority
?
999 Views
Last Modified: 2012-06-21
Hi friends,

I am having a problem with a simple select query which can be displayed as follows:

SELECT ID, NAME
FROM TABLE1
WHERE TABLE1.TEXTFIELD1 LIKE '%MySearchText%'

Since the Database is case sensitive I will have to use the UPPER function in the statement but the TEXT field type in the Database doesn't support this function.

Making database case insesitive in run time is not advisable since the database is shared in the network.

Is there anyone around who can help me?

0
Comment
Question by:juliusrai
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:tonyhowarth
ID: 11828604
Change the collation in the statement to an non case sensitive collation using the COLLATE function.

Tony
0
 

Author Comment

by:juliusrai
ID: 11829050
Thanks for participation tonyhowarth.

I got it how to do it.

the thing is if I CAST the field in VARCHAR within WHERE clause then I can use the UPPER function.

so the statement would look like this:

SELECT ID, NAME
FROM TABLE1
WHERE UPPER(CAST(TABLE1.TEXTFIELD1)) LIKE UPPER('%MySearchText%')

Thanks once again
0
 
LVL 2

Expert Comment

by:tonyhowarth
ID: 11829099
Yep, that's fine, but I would check the performance of the SQL with and without the UPPER.  Using a function in the where means that usage of indexes is adversely affected.

Tony
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:juliusrai
ID: 11829123
Please do it as I m not expert in SQL so I was also thinking about that.

Pleas let me know if you have better than this.

Also please give me the code using the COLLATE function if you can.

Thanks
0
 
LVL 2

Expert Comment

by:tonyhowarth
ID: 11829460
Doh....I may have mislead you slightly.  I went back to look at the code that I wrote to do this and it appears that the case sensitivity is handled on a Left Outer Join, as you can see below.

I am not sure you can do this on a WHERE clause.

SELECT Table1.*,Table2.* FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Code = Table1.Code_Ref COLLATE SQL_Latin1_General_CP1_CI_AS


Sorry
Tony
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 11866639
Closed, 125 points refunded.
Netminder
Site Admin
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question