I have to let people search for documents by date. The database is used by French-speakers as well as English-speakers. The search allows users to type in free text (because it's not just dates they might search by, though if they do type in something that's intended to be interpreted as a date, that's the only thing they're allowed to type in). So, I might get '23/Apr/2009' or '2009/04/23', for example. (I also have to allow them to submit 23.04.2009 and 23 04 2009 and any combination of the above!)
I have written a function which breaks the string into three bits, and then I work out if each bit is a year, or a month, or a day with simple rules (eg, if it's a number between 1900 and 2400, it must be the year component).
When it comes to the month, I do two things. One is obvious: is it a number between 1 and 12? The other is, is it a month name that appears in a list of 'allowed' month names. So my function does this at the start:
v_validmonths := 'january,february,march,april <and so on>';
v_validmonths := v_validmonths || ',janv,fevr,avr,mai, <and so on>';
Then I do this:
for x in 1 .. listlen(v_validmonths,',') loop
v_probemonth := listgetat(v_validmonths,x,',');
if instr(lower(v_partone),v_probemonth) > 0 then
v_month := extract (month from to_date(v_partone,'MON'));
end if;
end loop;
IE, I compare the first component of the string (v_partone) with the list of allowed month names. I repeat that for the other two parts of the supplied string. As you can see, if one of the parts of the supplied string matches an allowed month name, I then try to extract the month
number into a variable, using Oracle's own extract function.
And that's where my problem arises. Because although "aout" is valid French for August and is permitted by my list of valid months, the extract function fails to understand it. A simple
select extract (month from to_date('AOUT','MON')) from dual, for example, gives an ORA01843 (invalid month).
How can I get the code to work with French (and German, actually) months and month abbreviations whilst also being able to understand English ones? Is there an in-built function that would allow me to do this? Or is there some way neatly to say, 'yes: AOUT is in the list, and it's month 8' without me having to rely on the extract function to work it out?
(I realise that the business requirement here is very peculiar and your first reaction will probably be "this is horrible!". I will absolutely agree with you! But this is the requirement as has been explicitly specified by the MD and I can't negotiate it away, sadly.)