[Oracle] to_number() und der Dezimaltrenner
Achim Grolms
Hallo,
ich möchte mit Hilfe der Oracle PL/SQL Builtin Funktion
| to_number( wertvarchar2,
| maske,
| NLSparameter)
als VARCHAR2 vorliegende Zahlen ('14,9' beispielsweise) in NUMBER
umwandeln und dabei *explizit* den Dezimaltrenner (Komma oder Punkt)
über den dritten Parameter angeben.
Beispielsweise sieht das dann mit Komma als Trenner so aus:
| to_number( '14,9',
| '9990D99',
| 'NLS_NUMERIC_CHARACTERS='',.'' ');
Das funktioniert soweit wie gewollt, allerdings muss immer der zweite
Parameter Formatmaske angegeben werde, was damit auch mich die Anzahl
der Nachkommastellen festnageln lässt.(Irgendwas muss ich ja angeben).
Wie kann ich es aber anstellen, daß die Anzahl der Nachkommastellen
"beliebieg"[1] wird, also wie es bei dem Aufruf von
to_number('14.9');
wäre?
Dank und schöne Grüße,
Achim
[1] im Rahmen der von Oracle zur Verfügung gestellten Stellen
Achim Grolms
> Kathinka Wenz <no_e...@gmx.de> wrote:
>> to_number(translate(replace('14,9'),',',''),'.',','))
> *arg* Das ist natürlich genau verkehrt herum, das macht aus einem Punkt
> ein Komma. Also
> to_number(translate(replace('14,9'),'.',''),',','.'))
Hallo Kathinka,
Vielen Dank für Deine Mühe!
Leider geht die Idee genau am Problem vorbei.
Nachmal; Ich möchte dem 'to_number' *explizit* vorgeben,
was der Dezimaltrenner ist.
Das kommt daher: Clients mit *verschiedenen* NLS_LANG Einstellungen
(Webserver) möchten alle den Wert '14,9' in dieselbe NUMBER Spalte
schreiben. Je nach Kontext evaluiert to_number das dann zu
14.9 oder 149. Deshalb möchte ich diesen Kontext
('NLS_NUMERIC_CHARACTER') explizit mit dem dritten Parametert
festnageln.
Vielleicht fällt Dir/euch noch etwas ein?
Dank und schöne Grüße,
Achim
PS: das 'Wenz' im From header irritiert mich immer noch ein wenig.
Auch von mir einen herzlichen Glückwunsch!
Achim Grolms
> Achim Grolms <ac...@grolmsnet.de> wrote:
>> Vielen Dank für Deine Mühe!
> War nicht wirklich mühsam.
>> Leider geht die Idee genau am Problem vorbei.
>> Nachmal; Ich möchte dem 'to_number' *explizit* vorgeben,
>> was der Dezimaltrenner ist.
> Warum? Du kannst doch auch genauso gut den Dezimaltrenner immer in einen
> bestimmten umwandeln.
Stimmt. Das hilft mir aber nicht weiter, wenn to_number() auf Client A
das ',' und to_number auf Client B das '.' als Dezimaltrenner
verwendet.
>> Das kommt daher: Clients mit *verschiedenen* NLS_LANG Einstellungen
>> (Webserver) möchten alle den Wert '14,9' in dieselbe NUMBER Spalte
>> schreiben. Je nach Kontext evaluiert to_number das dann zu
>> 14.9 oder 149. Deshalb möchte ich diesen Kontext
>> ('NLS_NUMERIC_CHARACTER') explizit mit dem dritten Parametert
>> festnageln.
> Willst du sagen, dass er 14,9 teilweise nicht erkennt und 149
> einschreibt? Interessante Reaktion.
Schau Dir einen *deutschsprachigen* Client an, bei dem
'NLS_NUMERIC_CHARACTERS='',.'' ' :
|SQL> select to_number('14.9') from dual;
|select to_number('14.9') from dual
| *
|ERROR in Zeile 1:
|ORA-01722: invalid number
jetzt dasselbe auf einem englischsprachigen Client mit:
'NLS_NUMERIC_CHARACTERS=''.,'' ':
|SQL> select to_number('14.9') from dual;
|
|TO_NUMBER('14.9')
|-----------------
| 14.9
Der Dezimaltrenner ist also abhängig von der Sprache des
installierten Clients (Auf dem Webserver in meinem Falle)
bzw. dessen lokalen Einstellungen.
(Auf die Ich keinen Einfluss habe. Ich möchte *prinzipiell* von den lokalen
Einstellungen des Clients unabhängig sein, nämlich durch *explizite* Angabe
des Dezimaltrenners in meinen Konvertierungsfunktionen)
>> Vielleicht fällt Dir/euch noch etwas ein?
> So ganz hab ich dein Problem immer noch nicht verstanden, ich taste mich
> mal ran. Ich hab mit den NLS-Einstellungen noch nicht gespielt (und zur
> Zeit auch keinen Zugriff auf Oracle), wie reagiert er denn bei
> | to_number( '14,9',
> | '9990D99',
> | 'NLS_NUMERIC_CHARACTERS='',.'' ');
> auf die unterschiedlichen Clients?
In diesem Falle überschreibt der dritte Paramter ja die lokalen
Einstellungen des Clients. Damit *weiß* ich als Programmierer, wie
die Konvertierungsfunktion sich verhält.
> Es will in meinen Kopf nicht rein,
> das du mit diesem einen Befehl beide Fälle angedeckt hast, das
> entspricht doch nur der deutschen Einstellung, oder?
> Wenn du also eh unterscheiden musst, dann kannst du im einen Zweig den
> Punkt umwandeln und im anderen die Zahl halt so lassen, wie sie ist.
Also mal mit anderen Worten erklärt:
Ich weiß, das
1. Die Eingangsdaten als Strings im Format 'formkomma,nahcdemkomma'
vorliegen. (In dieser Form kommen sie aus einem CGI-Request und
werden von Perl per DBI ans RDBMS weitergereicht.
2. Ich weiß nicht mit Sicherheit, was am Client (Webserver) als
NLS_NUMERIC_CHARACTERS eingestellt ist, daher ist es vom Client
abhängig, welchen Trenner to_number() verwendet.
3. Daher möchte ich eine Funktion my_to_number, die *unabhängig*
von den lokalen NLS Einstellungen des Clients ist, implementiert
durch die Verwendung des driten Parameters von to_number
>> PS: das 'Wenz' im From header irritiert mich immer noch ein wenig.
> *g* Was meinst du, wie mir das geht.
Bin mal gespannt, wann Du Dir hier die ersten Ratschläge gibst :-)
Schöne Grüße,
Achim
--
Interferenzo Calzone and his crazy Eigenvalues,
die weltbekannte Elektrotechnikerband:
Achim Grolms
>> 1. Die Eingangsdaten als Strings im Format 'formkomma,nahcdemkomma'
>> vorliegen. (In dieser Form kommen sie aus einem CGI-Request und
>> werden von Perl per DBI ans RDBMS weitergereicht.
> Ok, jetzt weiß ich auch, wo mein Denkfehler war, ich habe vom Client zum
> Server gedacht. Der Weg ist aber andersrum. D.h. es liegt immer eine
> Zahl mit Komma vor,
Exakt, eine Folge von Ziffern mit einem Komma drinnen.
> nur ob "to_number" die auch als Zahl erkennt, ist
> unklar
Genau.
> Ganz simple Idee:
> begin
> to_number(zahl)
> except
> to_number(translate(replace(zahl),'.',''),',','.'))
> end
> Entweder, die Einstellungen sind deutsch, dann passt das erste. Oder sie
> sind englisch, dann knallt es, dann muss das , halt durch einen Punkt
> ersetzt werden. Vielleicht nicht elegant, sollte aber tun.
Hmmm. Ich habe den Exception Teil bisher immer für nur für kritische
Ausnahmebehandlung hergenommen, dabei möchte ich auch bleiben. (Obwohl
Deine Idee pfiffig ist :-) ) Was hältst Du denn
von meiner existierenden Lösung mit
|create or replace package body P_F
|is
|nls_deutsch constant varchar2(30) := 'NLS_NUMERIC_CHARACTERS='',.'' ';
|maske constant verchar2(30) := '99999999990D999999999';
.
.
.
|function c2n_in_deutsch( in_char_deutsch in varchar2 )
|return number
|is
| rueck number;
| linksformatiert varchar2( 40 );
| rechtssformatiert varchar2( 40 );
|
|begin
| linksformatiert := ltrim( in_char_deutsch );
| rechtssformatiert := rtrim( linksformatiert );
| rueck := to_number( replace( rechtssformatiert, '.', ''),
| maske,
| nls_deutsch );
| return rueck;
|end c2n_in_deutsch;
|end P_F;
Funktioniert wie gewollt, es stellt sich aber die Frage, wieveil '9' man
der maske spendieren darf|muss|sollte, damit die Anzahl der
Nachkommastellen in 'in_char_deutsch' "beliebig" wird?
Dank und schöne Grüße,
Achim Grolms
>> > begin
>> > to_number(zahl)
>> > except
>> > to_number(translate(replace(zahl),'.',''),',','.'))
>> > end
>>
>> Hmmm. Ich habe den Exception Teil bisher immer für nur für kritische
>> Ausnahmebehandlung hergenommen, dabei möchte ich auch bleiben.
> Selber schuld. Damit kann man sich oft das Leben leicht machen.
Kurzfristig ja. Der Preis ist schlechter les- und wartbarer Code,
da hier Sprachmittel der Fehlerbehandlung für Kontrollfluß
im 'normalen' Programteil mißbraucht werden.(IMHO)
[Mein Beispiel]
> Nur eine Kleinigkeit: Warum schreibst du nicht
[Dein Beispiel]
> Ist deutlich kürzer
Ja.
> und zumindest ich finde es nicht so verwirrend.
Das ist eine Frage der Klammerebenen.
Ich breche der Lesbarkeit wegen gerne den Code in Teilausdrücke auf.
Letztendes halte ich das aber für Geschmacksache.
> Naja, vielleicht bin ich auch ein bißchen minimalistisch.
Das lebe ich mit Perl aus ;-)
> Wobei mir auffällt, dass du keine Fehlerverarbeitung hast, ist das
> absicht?
Ja. Für Beispielcode für d.c.d.m, welcher
das Prinzip verdeutlichen soll,
halte ich alles weitere für überflüssig,
da es vom Wesentlichen ablenkt.
> Es könnte ja auch keine Zahl übergeben werden.
Ja. In die Produktivversion gehört selbstverständlich eine angemessene
Fehlerbehandlung.
>> Funktioniert wie gewollt, es stellt sich aber die Frage, wieveil '9' man
>> der maske spendieren darf|muss|sollte, damit die Anzahl der
>> Nachkommastellen in 'in_char_deutsch' "beliebig" wird?
> Eben. Dem Problem wirst du so nie los.
Aber das nackte to_number( VARCHAR2 ) kann doch auch mit beliebigen
Stellen umgehen. Darum müsste es doch auch bei expliziter
Trennerangabe möglich sein.
> Da gefällt mir meine Methode
> besser, da sie immer funktioniert. Aber es ist deine Entscheidung.
Nein, Deine Methode hat mich noch nicht überzeugt.
Trotzdem danke ich Dir für die Zeit und Mühe, die Du in Deine Antworten
investiert hast.
Schöne Grüße,
Achim
Achim Grolms
> Aber das nackte to_number( VARCHAR2 ) kann doch auch mit beliebigen
> Stellen umgehen. Darum müsste es doch auch bei expliziter
> Trennerangabe möglich sein.
Ich habs jetzt mal so implementiert:
create or replace package body P_F
is
nls_deutsch constant varchar2(30) := 'NLS_NUMERIC_CHARACTERS='',.'' ';
nls_anglo constant varchar2(30) := 'NLS_NUMERIC_CHARACTERS=''.,'' ';
function c2n_in_deutsch( in_char_deutsch in varchar2 )
return number
is
function c2n_in_deutsch( in_char_deutsch in varchar2 )
begin
return to_number( replace(rtrim(ltrim(in_char_deutsch) ),'.',''),
fmaske( in_char_deutsch, ','),
nls_deutsch );
end c2n_in_deutsch;
/* -------------------------------------------------------- */
function c2n_in_anglo( in_char_anglo in varchar2 )
return number
is
begin
return to_number( replace(rtrim(ltrim(in_char_anglo) ),',',''),
fmaske( in_char_anglo, '.'),
nls_anglo );
end c2n_in_anglo;
/* -------------------------------------------------------- */
function fmaske( in_char_wert in varchar2,
in_trenner in varchar2 )
return varchar2
is
kommaposition integer;
laenge integer;
begin
kommaposition := instr( in_char_wert, in_trenner );
laenge := length( in_char_wert );
if ( kommaposition > 0 ) then
return rpad( lpad( 'D', kommaposition + 1, '9'),
laenge + 1,
'9');
else
return rpad( '9', laenge, '9');
end if;
end fmaske;
/* -------------------------------------------------------- */
end P_F;
Schöne Grüße,
Dirk Mika
mal 'ne Frage:
Kannst Du evtl. deine Clients (Webserver) dazu bringen nach dem Connect
zur DB ein
alter session set nls_numeric_characters=',.';
abzusetzen?
Damit könntest Du dir die Angabe im "to_number" sparen.
Gruß
Dirk Mika
Achim Grolms
> Hallo,
> mal 'ne Frage:
> Kannst Du evtl. deine Clients (Webserver) dazu bringen nach dem Connect
> zur DB ein
> alter session set nls_numeric_characters=',.';
> abzusetzen?
Funktioniert.
Wieder den Wald vor Bäumen nicht gesehen!
Vielen Dank!
Dirk Mika
> Funktioniert.
> Wieder den Wald vor Bäumen nicht gesehen!
;-)
>
>
> Vielen Dank!
Nichts zu danken.
Gruß
Dirk Mika