get the string between 3rd and 4th space using regexp

i have one of my table column(SR) date in the below format:

SR Creation Time: 17-Feb-2022 11:37:31 CST

From this i need to extract the date(17-Feb-2022) only. I tried using regexp_substr with some option,but could not get the exact output. Can you please suggest.Thanks.

CodePudding user response：

I'd use @ekochergin's answer, but there is a way to do exactly what you asked...

SELECT
  regexp_substr(
    'SR Creation Time: 17-Feb-2022 11:37:31 CST',
    '([^[:space:]] )',  -- matches 1 or more non-space characters
    1,                  -- starts from 1st character of search string
    4                   -- returns 4th successful match
  )
FROM
  DUAL

https://dbfiddle.uk/?rdbms=oracle_21&fiddle=2d84559be9718e8bb4954a2c9afdd3f2

CodePudding user response：

Instead of counting spaces, you may just parse date out if date format is always same

SELECT regexp_substr('SR Creation Time: 17-Feb-2022 11:37:31 CST', '[0-9]{2}-[A-Za-z]{3}-[0-9]{4}')
  FROM dual;

CodePudding user response：

You simply need the TO_CHAR function -

SELECT TO_CHAR(SR_Creation_Time, 'DD-MON-YYYY')
  FROM YOUR_TABLE;