CODEWITHSUNDEEP
sqloracle-database
XXXX
XXXX

Reputation: 65

Converting international format numbers to the German format in oracle

I have this column named display.

Display
<5.78
<0.03/

I was trying to convert it into German format like it will show:

Display
5,78
0,03

Below is the query :

CREATE TABLE Customer (
    CustomerID int NOT NULL,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    display varchar(500),
    PRIMARY KEY (CustomerID)
);

INSERT INTO Customer VALUES (101, 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', 'Norway', '<5.78');
INSERT INTO Customer VALUES (102, 'Tom', 'Riddle', 'UK', 'London', '<0.03/');
INSERT INTO Customer VALUES (104, 'Tom', 'Riddle', 'UK', 'London', 'auto.met');

And the query I used for converting into German format using Oracle.

select FORMAT(cast(display as int),'N', 'de-de') from Customer;

It's giving me

ORA-00904: "FORMAT": invalid identifier.

I understand I need to replace then non numeric characters before formatting.

Select display, replace(replace(display, '<' , ' ') , '/' , ' ') from customer;

I tried many things but couldn't solve. Can someone please help with this?

Upvotes: 0

Views: 1188

Answers (3)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65323

If there's no dot but between digits for the whole data set, then use

SELECT TO_CHAR(
                 REGEXP_REPLACE(display,'[^0-9.]'),
                 'FM99999990D99','NLS_NUMERIC_CHARACTERS=,.'
       ) AS "Display" 
  FROM customer

in order to extract the parts such as 5.78, 0.03 etc. , then convert them to the desired numeric format(5,78, 0,03 etc).

Edit : if lately added type of values exist for the display, then convert the query to a conditional one such as

SELECT CASE WHEN REGEXP_REPLACE(display,'[^0-9]') IS NOT NULL THEN
       TO_CHAR(
                 REGEXP_REPLACE(display,'[^0-9.]'),
                 'FM99999990D99','NLS_NUMERIC_CHARACTERS=,.'
       ) END AS "Display" 
  FROM customer

Demo

Upvotes: 0

MT0
MT0

Reputation: 168071

You can trim the unexpected characters from it and then use TO_NUMBER to convert it from a string to a number and TO_CHAR to convert the number back to a string and specify the NLS_NUMERIC_CHARACTERS to set the radix and thousands separator for each conversion:

SELECT display,
       TO_CHAR(
         TO_NUMBER(
           RTRIM( LTRIM( display, '<' ), '/' ),
           '99999990D99',
           'NLS_NUMERIC_CHARACTERS=.,'
         ),
         'FM99999990D99',
         'NLS_NUMERIC_CHARACTERS=,.'
       ) AS display_german
FROM   Customer;

Which outputs:

DISPLAY DISPLAY_GERMAN
<5.78 5,78
<0.03/ 0,03

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142788

See if this helps. Basically, it

  • replaces all that's not a digit or a dot with an empty string (that's regexp)
  • replaces dots with commas

So:

SQL> select customerid,
  2    display,
  3    replace(regexp_replace(display, '[^[:digit:]\.]', ''), '.', ',') new_display
  4  from customer;

CUSTOMERID DISPLAY    NEW_DISPLAY
---------- ---------- ------------
       101 <5.78      5,78
       102 <0.03/     0,03

SQL>

Upvotes: 2

Related Questions