Solved

making search better with LIKE

Posted on 2011-09-15
9
242 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 62
selective rebuild of SQL Tables in scheduled job 10 46
SQL parsing XML works but want to do it another way 4 47
SQL Server Serialization error 8 44
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 Backup & Restore 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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

737 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