SQL 7 - Text Field - Case Insensitive Search - How

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 2000 Forums

SQL Server Development (2000)

SQL 7 - Text Field - Case Insensitive Search - How

Author

Topic

SqlLearningCurve
Starting Member

2 Posts

Posted - 2005-04-05 : 09:36:25

Hi All,

I need to pick you brain as SQL guru's. I've been asked to help in a project and am stuck on something that should be simple.

I'm coding in classic ASP with a SQL 7 db (as the server is v.old). SQL was installed as case sensitive. I have to write a search function for it without writting a component as I have no development environment (as there is no money).

I have been making varchar fields lowercase and the search input lowercase which is fine. However, I am now having the do the same on a text field. The SQL lower function doesn't work with text fields. I
can't use the Full Text Catalogue as Microsoft Search isn't and can't be installed.

Have you got any ideas that can help or point me in the right direction.

Thanks,
SqlLearningCurve

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-05 : 09:49:54

Try to convert text as Varchar

Madhivanan

Failing to plan is Planning to fail

SqlLearningCurve
Starting Member

2 Posts

Posted - 2005-04-05 : 10:01:55

Thanks for getting back so quickly. In my where clause I am using:

LOWER(CAST(p.PostContent AS varchar(500))) LIKE '%test%'

However, I have concerns over what will happen if the text field has more than 500 characters. Will it just crop the field to the first 500?

Thanks,
SqlLearningCurve

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-07 : 02:30:01

Yes, you need to increase the width

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-07 : 06:20:14

you could use COLLATE in your search. i don't know if it's supported in sql 7.
if it isn't go with what madhivan suggested.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources