CODEWITHSUNDEEP
sqlregexoracle-databaseparsingextract
SelVazi
SelVazi

Reputation: 16033

Extract date from string using regular expression - SQL ORACLE

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!

Upvotes: 0

Views: 1276

Answers (2)

Alex Poole
Alex Poole

Reputation: 191255

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

Upvotes: 3

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

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

Live Demo

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

Upvotes: 1

Related Questions