LIKE query not working as expected

Question

I have an SQL Query that looks like:

SELECT "medical_interventions"."id" AS t0_r0, "medical_interventions"."refcode" AS t0_r1, "medical_interventions"."intervention_tid" AS t0_r2, "medical_interventions"."medical_intervention_category_id" AS t0_r3, "medical_interventions"."created_at" AS t0_r4, "medical_interventions"."updated_at" AS t0_r5, "translations"."id" AS t1_r0, "translations"."lang" AS t1_r1, "translations"."text" AS t1_r2, "translations"."created_at" AS t1_r3, "translations"."updated_at" AS t1_r4 FROM "medical_interventions" LEFT OUTER JOIN "translations" ON "translations"."id" = "medical_interventions"."intervention_tid" AND "translations"."lang" = 'fr' WHERE (medical_intervention_category_id =7 AND text like '%é%')

I have a text in there labeled évaluer

When I search fore just é as in the query above, I get évaluer as I expect. However, if I try év:

SELECT "medical_interventions"."id" AS t0_r0, "medical_interventions"."refcode" AS t0_r1, "medical_interventions"."intervention_tid" AS t0_r2, "medical_interventions"."medical_intervention_category_id" AS t0_r3, "medical_interventions"."created_at" AS t0_r4, "medical_interventions"."updated_at" AS t0_r5, "translations"."id" AS t1_r0, "translations"."lang" AS t1_r1, "translations"."text" AS t1_r2, "translations"."created_at" AS t1_r3, "translations"."updated_at" AS t1_r4 FROM "medical_interventions" LEFT OUTER JOIN "translations" ON "translations"."id" = "medical_interventions"."intervention_tid" AND "translations"."lang" = 'fr' WHERE (medical_intervention_category_id =7 AND text like '%év%')

I get absolutely no results.

Is there something I do not understand about LIKE? Is there an alternate function I could use for searches?

AR:
    def self.find_by_search_query_and_problem_id(search_query, problem_id)
        problem = Problem.find(problem_id)
        category = ProblemCategory.find(problem.category_id)
        medIntCategory = MedicalInterventionCategory.find_by_category_text(category.category.text)

        search = search_query
        query = "medical_intervention_category_id =" + medIntCategory.id.to_s + " AND text like ?"
        return self.includes(:intervention).where(query, "%#{search}%").references(:intervention)
    end

In the controller

def search
    @interventions = MedicalIntervention.find_by_search_query_and_problem_id(URI.unescape(params[:search_query]),params[:problem_id])
end

In JS:

getInterventionsFromSearch: function(search_query, problem_id) 
{
    var interventions = new MedicalIntervetionsCollection();
    // fetch data on url AJAX
    if(search_query != "")
    {
        interventions.url = "medical_interventions_search?search_query="+encodeURIComponent(search_query) + 
        "&problem_id=" + problem_id;
        interventions.fetch();
    }
    return interventions;
}

http://sqlfiddle.com/#!7/9fbfa/1: I don´t think the problem is related to the LIKE.... something else is going on.... encoding? — gabrielhilal, Jun 19 '15 at 19:34

score 1 · Accepted Answer · edited May 23 '17 at 12:14

See this post: How to SQL compare columns when one has accented chars?

Another idea:

There is an easy solution, but not very elegant.

Use the REPLACE function, to remove your accents. Exemple:

SELECT YOUR_COLUMN FROM YOUR_TABLE WHERE replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace( lower(YOUR_COLUMN), 'á','a'), 'ã','a'), 'â','a'), 'é','e'), 'ê','e'), 'í','i'),
'ó','o') ,'õ','o') ,'ô','o'),'ú','u'), 'ç','c') LIKE 'SEARCH_KEY%'

Where SEARCH_KEY is the key word that you wanna find on the column.

Or:

A possible solution would be a User-Defined-Function (UDF). There is a document here describing how to create such a function for SQLite in PHP. You could write a function called DROPACCENTS()

Sorry about that, I updated my response with some ideas for you. — BrianAtkins, Jun 19 '15 at 20:23

LIKE query not working as expected

1 Answers1