Replace character with multiple characters

Question

In my application date formats are configurable and it is stored in one of the following formats in the table:

d.m.Y --> represents dd.mm.rrrr
d.m.y --> represents dd.mm.rr
Y.m.d --> represents rrrr.mm.dd
y.m.d --> represents rr.mm.dd

I need to use it in my queries and procedures to show the dates in the format specified by user which can be done by TO_CHAR(any_date, date_format)

but, I will need to convert these values to the format that Oracle can recognize.

Currently, I am using REPLACE to achieve this as follows:

TO_CHAR(<my_date_column>, REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE('<date_format>', 
                                            'd', 'dd'), 
                                        'm', 'mm'), 
                                    'Y', 'rrrr'), 
                                'y', 'rr'))

I am curious to know if there is another way of doing the same. May be using REGEXP_REPLACE or some other way.

*"if there is another way of doing the same"* You mean apart from using the Oracle built-in NLS settings, which can be configured at the session level or in the client? — APC, Sep 19 '19 at 16:37
If you can't just use Oracle's formats to begin with, I suggest creating a table to store the translations from your format to Oracle's format. E.g., "d.m.Y" -> "dd.mm.rrrr". Your code will be shorter and more flexible (e.g., you could add a time component to the translation by changing data instead of changing code). — Matthew McPeak, Sep 19 '19 at 16:42
No, table creation is not allowed and I just need to know if there is another way possible for it or not. I am thinking of regexp_replace to achieve the same. But dont know how to use it. — Popeye, Sep 19 '19 at 17:32
You mean a _single_ `regexp_replace`? I mean, of course you could do it with 4 of them, but that's basically just the same as using regular old `replace`. Doing it with a single `regexp_replace` would be a fun intellectual exercise, but I'd shoot the developer who put something like that into production! — Matthew McPeak, Sep 19 '19 at 19:59

score 1 · Answer 1 · answered Sep 19 '19 at 18:54

Are there any other possible formats then the 4 you listed in your question? If not that the decode of VBokšić is a valid answer.

Your replace is just as hardcoded.

I suggest to not store the format the user entered at all (neither the text value 19.09.19 nor the used format dd.mm.rr): let the user interface handle the interpretation and store a real DATE. Split your API from your data model.

score 0 · Answer 2 · answered Sep 19 '19 at 16:51

You should start by looking at the definition of the column in the table. Is it a Date type field, a Number field, a varchar2 field?

If any_date is in Date format, then you can just use to_char: to_char(any_date, 'd.m.Y')

if the field,any_date is in number(8,0) format, then, you need to understand the number format and translate it to char. Suppose any_date is a number as YYYYMMDD, then use to_char(any_date) to get the number as YYYYMMDD.
If any_date is in number format as YYYYMMDD, and you want it as d.m.Y, then, your best bet is to convert the number to char, then convert to date, then convert back to character: to_char(to_date(to_char(any_date), 'YYYYMMDD), 'd.m.Y')

If you date is in varchar2 format, then again, the best option is to convert to date format and convert back to varchar2: to_char(to_date(any_date, ''), '')

My date column is of date type not number or varchar2. – Popeye Sep 19 '19 at 17:37 — Popeye, Sep 19 '19 at 17:37

score 0 · Accepted Answer · answered Sep 19 '19 at 17:17

0

Would use of decode be an option for you?

TO_CHAR(<my_date_column>, decode(<date_format>, 'd.m.Y', 'dd.mm.rrrr'
                                              , 'd.m.y', 'dd.mm.rr'
                                              , 'Y.m.d', 'rrrr.mm.dd'
                                              , 'y.m.d', 'rr.mm.dd'))

answered Sep 19 '19 at 17:17

VBoka

8,995
3
16
24

Why can't you simply create a view and use it in your code. In anyway, the code to display a formatted date must reside in application side and not in the database as Oracle doesn't store date in any format. It uses `Type 12` and `Type 13` data types to store dates https://stackoverflow.com/a/36059638/3989608 – Lalit Kumar B May 13 '20 at 13:08

Replace character with multiple characters

3 Answers3