// This function normalize fields and strings with or without accents, to where (commands sql).
// This functions converts fields and strings with special characters into fields and strings with
characters normalized and,
// convert this to uppercase
function normalize($field,$string,$is_normalized,$type){
$characters_with_accents = "?????????Ç?"; // characters with accents
$characters_norm = "aeiouaeiouacouAEIAEOUAEIOUACOU"; // characters normalized
if ($is_normalized) // if is true, the field is normalized
$field_normalized = $field;
else
// normalize the field
$field_normalized = "upper(translate
(".$field.",'".$characters_with_accents."','".$characters_norm."'))";
$string_normalized = strtoupper(strtr(trim
($string),$characters_with_accents,$characters_norm));
if (strcmp($type,"%like%") == 0) // the type of comparasion is 'like', in both sides (left
and right)
return $field_normalized." like '%".$string_normalized."%'";
elseif (strcmp($type,"like%") == 0) // the type of comparasion is 'like', in left side
return $field_normalized." like '".$string_normalized."%'";
elseif (strcmp($type,"%like") == 0) // the type of comparasion is 'like', in right side
return $field_normalized." like '%".$string_normalized."'";
elseif (strcmp($type,"=") == 0) // the type of comparasion is '='
return $field_normalized." = '".$string_normalized."'";
}
?>
Examples of use:
$sql1 = "select * from people where ".normalize("name","S?? Concei?",false,"%like%");
$sql2 = "select * from keywords where ".normalize("key","web",true,"like%");
$sql3 = "select * from letters where ".normalize("first_letter","a",true,"=");
?>
This examples gives:
$sql1 = "select * from people where upper(translate(name,'?????????Ç?
?,'aeiouaeiouacouAEIAEOUAEIOUACOU') like '%SONIA CONCEICAO%'";
$sql2 = "select * from keywords where key like 'WEB%'";
$sql3 = "select * from letters where where first_letter='A'";
This is very useful in command's Sql, and works fine with Oracle.
Is so mutch useful in search's where the field and respective string is not normalized.