making search better with LIKE

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?
LVL 1
rafaelrglAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rafaelrglAuthor Commented:
is changing this on sql database property changes any data structure? or anything else beside the way where like works?
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
rafaelrglAuthor Commented:
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
 
rafaelrglAuthor Commented:
sorry that query:
 WHERE your_column COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%especializacao%'

returns all with accent or not
0
 
rafaelrglAuthor Commented:
thanks so muchhh :)))
how long do u work with sql? you are genius
0
 
Kevin CrossChief Technology OfficerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.