Solved

making search better with LIKE

Posted on 2011-09-15
9
238 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
  • 5
  • 4
9 Comments
 
LVL 59

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 59

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 59

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 59

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 59

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

808 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