CODEWITHSUNDEEP
oracle-database
Frank XG
Frank XG

Reputation: 11

A Oracle query with accent

this is my problem, I got a database in ISO-8859-1 and I have my webpage in UTF-8, I want to remove the accents from my queries I'm able to find names without accents but if they have them I'm unable to find them (down there I have a name with accent, I can't find the name) Help, please, I'm dying here... I have this:

$el=array();     //<----------------------------------------------------vowels to remove
$el[]=iconv('UTF-8','ISO-8859-1','á');
$el[]=iconv('UTF-8','ISO-8859-1','é');
$el[]=iconv('UTF-8','ISO-8859-1','í');
$el[]=iconv('UTF-8','ISO-8859-1','ó');
$el[]=iconv('UTF-8','ISO-8859-1','ú'); 

$string='Francisco Gutiérrez';      //<----------------------------------------target


$string=strtolower($string);     ///<----------------------------------string to iso 
$string=iconv('UTF-8','ISO-8859-1',
                    $string);


$tem3="SELECT  nom||' '||app||' '||apm as NAME 
       FROM STUDENTS 
       where 

      (
       upper(
            replace(
            replace(
            replace(
            replace(
            replace(
         lower(NAME),'".$el[0]."','a'),
                       '".$el[1]."','e'),
                       '".$el[2]."','i'),
                       '".$el[3]."','o'),
                       '".$el[4]."','u')
            ) 

       like '%'||
        upper(
            replace(
            replace(
            replace(
            replace(
            replace(
         '".$string."','".$el[0]."','a'),
                       '".$el[1]."','e'),
                       '".$el[2]."','i'),
                       '".$el[3]."','o'),
                       '".$el[4]."','u')
             )||'%'
     )";

Upvotes: 1

Views: 1728

Answers (1)

Joop Eggen
Joop Eggen

Reputation: 109603

You can do SQL CONVERT(string, destination_encoding, source_encoding), which gives ? for unconvertible chars, you could drop with a replace.

If you do

$string = iconv('UTF-8','ASCII//TRANSLIT', $string);

SELECT CONVERT(nom, 'US7ASCII', 'WE8ISO8859P1') 
FROM STUDENTS LIKE ... $string ... ;

You fall back on ASCII which should do nicely.

ISO-8859-1 might do too.

Upvotes: 3

Related Questions