Extract date from string using regular expression - SQL ORACLE

Question

Say I have a field numCommande with a string "1610131223ZVV40" where 161013 is a date in form yymmdd.

Is there any way in SQL to extract that 13/10/2016 date from the string field ?

Thanks!

Is it always the first six characters? No need for regular expressions if so, substr() and to_date() would be enough. What have you tried so far? — Alex Poole
– Alex Poole, Commented Mar 20, 2018 at 10:20

Alex Poole · Accepted Answer · 2018-03-20 10:24:34Z

3

If the 'date' is always the first six characters, you can extract those with a plain substr() call:

substr(numCommande, 1, 6)

which gives you '161013'; and then convert that string to a date with a suitable format model:

select to_date(substr(numCommande, 1, 6), 'RRMMDD') from your_table;

Quick demo with static value instead:

select to_date(substr('1610131223ZVV40', 1, 6), 'RRMMDD') from dual;

TO_DATE(SU
----------
2016-10-13

answered Mar 20, 2018 at 10:24

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

1 Comment

Upvoting for use of RR over YY

Jay Shankar Gupta · Accepted Answer · 2018-03-20 10:24:21Z

1

SELECT TO_CHAR(TO_DATE(SUBSTR(Column_Name,1,6), 'YYMMDD'),'DD/MM/YYYY') FROM TableName

Live Demo

http://sqlfiddle.com/#!4/ce715/5

answered Mar 20, 2018 at 10:24

Jay Shankar Gupta

6,1281 gold badge15 silver badges31 bronze badges