Getting formatted string in Oracle Sql query

Question

my query is something like this

Select date_value from Tab1;

The output looks like this (date_value is a Varchar2 column)

 2008-2009
 2009-2010
 2007-2009

I am trying to some formatting on the column date_value,so that the output looks like this

 2008-09
 2009-10
 2007-09

Is there a way to do this as part of the above query using some Split/Join inbuilt oracle functions. I was exploring about Instr and Substr, but i am not sure how to do this jsut in that query.

@cularis has the answer, but keep in mind, this is not a normal way to store data. If you had two columns -- ex: begin_year, and end_year, then you'd be able to perform date functions, etc. And of course, you'd still be able to output the 'YYYY-YY' format whenever you wanted to. — Chains
– Chains, Commented Aug 3, 2011 at 16:35
ya i understand what you are saying. since i am running my query on some existing database, don really have much option to change. — user747858
– user747858, Commented Aug 3, 2011 at 16:41

Jacob · Accepted Answer · 2011-08-03 16:25:56Z

8

SELECT SUBSTR(date_value,1,5) || SUBSTR(date_value,8,2) FROM tab1

This should work for the next ~8000 years.

answered Aug 3, 2011 at 16:25

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

APC · Accepted Answer · 2011-08-03 17:03:17Z

If you're doing this on a 10g database you can use Regular Expressions.

SQL> select dcol
  2         , regexp_replace(dcol
  3                          , '([[:digit:]]+)\-([[:digit:]]{2})([[:digit:]]{2})'
  4                          , '\1-\3') as regexp_replace
  5  from t79
  6  /

DCOL                           REGEXP_REPLACE
------------------------------ ------------------------------
2008-2009                      2008-09
2009-2010                      2009-10
2007-2009                      2007-09

SQL>

Of course, the usual caveat about regex applies here, although it does have one distinct advantage: Y10K compliance :)

SQL> select dcol
  2         , regexp_replace(dcol
  3                          , '([[:digit:]]+)\-([[:digit:]]+)([[:digit:]]{2})'
  4                          , '\1-\3') as regexp_replace
  5  from t79
  6  /

DCOL                           REGEXP_REPLACE
------------------------------ ------------------------------
2008-2009                      2008-09
2009-2010                      2009-10
2007-2009                      2007-09
10199-10200                    10199-00
887-888                        887-88

SQL>

Collectives™ on Stack Overflow

Getting formatted string in Oracle Sql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related