I have the following text from a column called 'subject':

Standard WiFi Install - (Broadband) by HS&S - Job No:VR041135037 on 2022-01-14

I need to extract the ID (VR041135037) and the date (2022-01-14) from the subject column.

What query can I write to go about doing this?

EDIT: This is the column from the table I have:

Thanks

CodePudding user response：

We can try using the SUBSTRING() function in regex mode with the help of a capture group:

SELECT
    Subject,
    SUBSTRING(Subject FROM 'Job No:([^[:space:]] )') AS ID,
    SUBSTRING(Subject FROM '\y[0-9]{4}-[0-9]{2}-[0-9]{2}$') AS date
FROM yourTable;

CodePudding user response：

You could nest SPLIT_PART function as well:

with my_data as (
  select 'OPALS INSTALL by HS&S - Job No:VR041613130 on 2022-03-17' as col1 union all
  select 'Standard WiFi Install - (Broadband) by HS&S - Job No:VR041729247 on 2022-03-17' union all
  select 'OPALS INSTALL by HS&S - Job No:VR041665578 on 2022-03-18'
  )
select col1,
 split_part(split_part(col1, 'Job No:', 2), ' ', 1) as job, 
 split_part(split_part(col1, 'Job No:', 2), ' on ', 2) as date
from my_data;