#6 WP content search is case sensitive

closed-fixed
nobody
None
5
2011-10-03
2011-09-21
Bob Osola
No

Default WP search returns "no match" if searching on "term" where a post and/or its title contains "Term".

Reason: the collation on certain columns in table 'wp_posts' is set to Latin1_General_BIN. You can check this by running: EXEC sp_help 'wp_posts'

Possible solution: if you change the collation on 'post_content' & 'post_title' (plus maybe 'post_excerpt'??) to Latin1_General_CI_AS then the search will work in the usual expected case-insensitive way.

ALTER TABLE wp_posts ALTER COLUMN post_content varchar(max)COLLATE Latin1_General_CI_AS
GO
ALTER TABLE wp_posts ALTER COLUMN post_content varchar(max)COLLATE Latin1_General_CI_AS
GO
ALTER TABLE wp_posts ALTER COLUMN post_excerpt varchar(max)COLLATE Latin1_General_CI_AS
GO

Unwanted side effects after performing the above have not been found so far

Discussion

  • OmniTI
    OmniTI
    2011-10-03

    The translations layer has been adjusted to properly obey the DB_COLLATE value if set, or otherwise to use database_default. This means you can alter the collate value to one appropriate for your situation. However this will only affect new installs and you'll need to manually alter columns in order to have it work properly. Forcing all users to use Latin1_General_CI_AS is not appropriate because it will mean other character sets will not sort or search properly.

     
  • OmniTI
    OmniTI
    2011-10-03

    • status: open --> closed-fixed