Compare Strings Ignoring Accents in SQL (Oracle)

SQL Server and Oracle query ignoring accents

One issue is that Microsoft SQL Server did not have a translate function until 2017. It does now, but since it doesn't work for you, you are probably not on this version yet.

You can do a nested replace instead. This is not difficult but is tedious to write. Once it is written and tested, it will be fine.

The Microsoft SQL Server documentation explains this: https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql

You also should be aware of the character encoding that is being used in Oracle and SQL Server. With the translate and replace functions you should be OK, but if you ever transfer data via files it will be important. I have described more of this at: http://www.thedatastudio.net/dodgy_characters.htm

Here's an example for the first few characters you want to translate:

select
  replace
  (
    replace
    (
      replace
      (
        replace
        (
          'ABÇDÉFGHÍJÁBÇDÉFGHÍJ', 'Á', 'A'
        ), 'Ç', 'C'
      ), 'É', 'E'
    ), 'Í', 'I'
  ) as clean_keyword;

Just substitute your keyword for 'ABÇDÉFGHÍJÁBÇDÉFGHÍJ'.

The result is:

ABCDEFGHIJABCDEFGHIJ

There is an example on https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql too.

Compare strings ignoring accented characters

You can use java Collators for comparing the tests ignoring the accent, see a simple example:

import java.text.Collator;

/**
 * @author Kennedy
 */
public class SimpleTest
{

  public static void main(String[] args)
  {
    String a = "nocao";
    String b = "noção";

    final Collator instance = Collator.getInstance();

    // This strategy mean it'll ignore the accents
    instance.setStrength(Collator.NO_DECOMPOSITION);

    // Will print 0 because its EQUAL
    System.out.println(instance.compare(a, b));
  }
}

Documentation: JavaDoc

I'll not explain in details because i used just a little of Collators and i'm not a expert in it, but you can google there's some articles about it.

Ignore accents on search using linq to entities (EF) and Oracle 12.1

I've used oracle NLS session parameters to resolve my issue.

 if(condition == true)
   AlterSortSession(context);

 public void AlterSortSession(MyContext context)
 {
   var connection = (OracleConnection)context.Database.Connection;
   connection.StateChange += AlterSortSession;
 }

 private static void AlterSortSession(object sender, StateChangeEventArgs e)
 {
  if (e.CurrentState != ConnectionState.Open)
    return;

  var connection = (OracleConnection)sender;
  OracleGlobalization info = connection.GetSessionInfo();

  info.Sort = "XGERMAN_DIN_AI";
  info.Comparison = "LINGUISTIC";

  connection.SetSessionInfo(info);
}

Documentation is available here for OracleGlobalization

How do I perform an accent insensitive compare (e with è, é, ê and ë) in SQL Server?

Coerce to an accent insensitive collation

You'll also need to ensure both side have the same collation to avoid errors or further coercions if you want to compare against a table variable or temp table varchar column

and because the constant value will have the collation of the database Update: only for local variables, not for constants nope, not even then

SELECT *
FROM Venue
WHERE
   Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI

Remove accents from string in Oracle

You can use TRANSLATE(your_string, from_chars, to_chars) https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions196.htm

Just put all chars with accents in from_chars string and their corresponding replacement chars in to_chars.

---

Related Topics