Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

making search better with LIKE

Posted on 2011-09-15
9
Medium Priority
?
251 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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

618 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