Solved

making search better with LIKE

Posted on 2011-09-15
9
243 Views
Last Modified: 2012-05-12
Hi, i want to search the content on some rows. i have an sql query that uses like but some times is not working since in Portuguese we have a lots accents like á, â, à. so i want the like behave like this:)

if someone is searching for the work:       especialização

the like should return results for all:  especializaçao, especializacão, especialização, and also the usual results for like:     testespecialização.

any help?
0
Comment
Question by:rafaelrgl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36546742
You need to set the collation to NOT be "accent sensitive" then it will work. If you cannot or do not want to change the collation for that column, you can use the COLLATE operator in conjunction with your LIKE.

WHERE your_column COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%especialização%'
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36546745
And you can always pick a collation more appropriate for you like the one for Portugese. The key is to make sure the AI part is there versus AS which is "accent sensitive" -- http://msdn.microsoft.com/en-us/library/ms184391.aspx
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 36546934
is changing this on sql database property changes any data structure? or anything else beside the way where like works?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36547008
If you set it at the database level that will be the default. If you already have objects dependent on the current collation, then you may get a failure trying to change this globally. You can always set a specific column to the collation you need if that is the case.
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 36547011
i change the property for collate on the sql database and try make where mycolumn like '%especializacao%'

and did not return anything, but when i added also on the query WHERE your_column COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%especialização%'

then i works. any idea why did not work changing the property on the database instead of using on the query?
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 36547015
sorry that query:
 WHERE your_column COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%especializacao%'

returns all with accent or not
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36547027
The COLLATION must not have take effect on the column. Check that it says <database_default> on that column and then try REFRESH of database or disconnect/reconnect.
0
 
LVL 1

Author Closing Comment

by:rafaelrgl
ID: 36547056
thanks so muchhh :)))
how long do u work with sql? you are genius
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36547101
Thank you for the compliment! I feel like I have a lot more to learn, but you pick a few things in 12 years. ;)
Best regards and happy coding,

Kevin
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

691 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