CODEWITHSUNDEEP
sqloracle-database
TimLer
TimLer

Reputation: 1360

oracle: format a number with slash

I have a column like this:

Productnr
1
25
260
3700
51126

and I should write a query to display the column like this:

productnr
0/01
0/25
2/60
37/00
511/26

Upvotes: 2

Views: 218

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would use to_char() and some string and arithmetic operations:

replace(to_char(Productnr / 100, 'FM99990.00'), '.', '/')

Here is a db<>fiddle.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You could use:

SELECT
    Productnr,
    REGEXP_REPLACE(
        SUBSTR('000' || Productnr,
               -GREATEST(3, LENGTH(Productnr)),
               GREATEST(3, LENGTH(Productnr))),
        '(..)$',
        '/\1'
    ) AS Productnr_out
FROM yourTable;

This approach first left pads the input with zeroes to a length of 3. Then, it inserts a forward slash before the final two characters using a regex replacement.

Upvotes: 1

Related Questions