CODEWITHSUNDEEP
sqloracle-databasenumber-formatting
Mario Alexander
Mario Alexander

Reputation: 37

Number formatting in Oracle SQL

I have some problem here. I got to SELECT a column which the result i.e. '01201698765'. How to split this number to becoming like this : '01.2016.98765'.

I've used TO_CHAR, but the '0' (zero) number at the front was gone.

Upvotes: 1

Views: 89

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could use:

  • SUBSTR
  • concatenation operator ||

For example,

SQL> WITH sample_data AS(
  2  SELECT '01201698765' num FROM dual
  3  )
  4  --end of sample_data mimicking real table
  5  SELECT num,
  6         substr(num, 1, 2)||'.'||substr(num, 3, 4)||'.'||substr(num, 7) num_formatted
  7  FROM sample_data;

NUM         NUM_FORMATTED
----------- -------------
01201698765 01.2016.98765

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269453

Assuming the column is a string, just use string operations:

select substr(col, 1, 2) || '.' + substr(col, 3, 4) + '.' + substr(col, 5, 5)

Upvotes: 0

Related Questions