Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Search question - case sensitive
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Search question - case sensitive
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
wb-in-wpb View Drop Down
Newbie
Newbie


Joined: 16 April 2008
Status: Offline
Points: 17 		Post Options Post Options   Thanks (0) Thanks(0)   Quote wb-in-wpb Quote  Post ReplyReply Direct Link To This Post Topic: Search question - case sensitive
    Posted: 17 September 2009 at 8:51pm
I had come across this when I initially setup an internal forum. I posted the following message:
 
I never changed anything and never had any further issue but I am having to deal now with searches that are not returning the posts I need because the search is running case sensitive. Searching for "green" will not return "Green".
 
I searched back on here and found this very similar post (problem with exact same fieldname):
 
The advice at the end of that is to run:
    ALTER database [YOURDBNAME] COLLATE Latin1_General_CI_AI
 
Well I double checked my database and the current collation is already case insensitive:
      SQL_Latin1_General_CP1_CI_AS
 
With my current collation, should I really be getting the search behavior that I am? Is the accent sensitive setting working against me as well? I will try altering the collation but I want to get a little input or reassurance before I do that.
 
I am running version 9.54 but I don't think that being a couple of revisions behind would matter for this issue.
Back to Top
wb-in-wpb View Drop Down
Newbie
Newbie


Joined: 16 April 2008
Status: Offline
Points: 17 		Post Options Post Options   Thanks (0) Thanks(0)   Quote wb-in-wpb Quote  Post ReplyReply Direct Link To This Post Posted: 18 September 2009 at 9:46pm
I found the problem!! In SQL 2005 the columns can have a different collation than the parent database which can in turn be set different from the server collation. The columns were set to a binary collation (therefore case sensitive). I do not remember changing the collation for the database after it was created. It seems that the columns inherited from the server collation instead of the database collation.
 
I changed the collation on the message text column and the topic subject column and now my searches work properly. I still need to change the collation on the author username but I have to take care of a couple of duplicate records that would result from the change first.
 
Are these three columns sufficient to change or should I target others as well?
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post Posted: 21 September 2009 at 12:22pm
As long as the fields holding the Topic Subject and Post are not case senstive you should be fine.

You may want to keep the Username case sensitive.
Web Wiz Forums Hosting
ASP.NET Web Hosting
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.