Question 114042 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Extract string from char var column and date from same column

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:

Subject
OPALS INSTALL by HS&S – Job No:VR041613130 on 2022-03-17
OPALS INSTALL by HS&S – Job No:VR041613130 on 2022-03-17
Standard WiFi Install – (Broadband) by HS&S – Job No:VR041729247 on 2022-03-17
Standard WiFi Install – (Broadband) by HS&S – Job No:VR041729247 on 2022-03-17
OPALS INSTALL by HS&S – Job No:VR041665578 on 2022-03-18
OPALS INSTALL by HS&S – Job No:VR041665578 on 2022-03-18

Thanks

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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;
    col1 job date
    OPALS INSTALL by HS&S – Job No:VR041613130 on 2022-03-17 VR041613130 2022-03-17
    Standard WiFi Install – (Broadband) by HS&S – Job No:VR041729247 on 2022-03-17 VR041729247 2022-03-17
    OPALS INSTALL by HS&S – Job No:VR041665578 on 2022-03-18 VR041665578 2022-03-18
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search