I have the following postgresql :
CREATE TABLE test (
id INT,
description TEXT
);
INSERT INTO test VALUES
(1, 'Some text'),
(2, '123 blabla The average processing time for this type of request is 5 days. blabla'),
(3, 'blalbla The average processing time for this type of request is 5 days.
This delay is reduced to 1 day for requests with high or critical priority. blabla'),
(4, 'blalbla The average processing time for this type of request is 7 days.
This delay is reduced to 2 day for requests with high or critical priority. blabla'),
(5, 'blabla The average processing time for this type of request is 3 days. blabla');
I need to get the following output :
ID | SLA text | days |
---|---|---|
1 | No | |
2 | Yes | 5 |
3 | Yes | 1/5 |
4 | Yes | 2/7 |
5 | Yes | 3 |
For the moment, I’m able to tell if the field contain a SLA value using :
SELECT
id,
CASE
WHEN regexp_replace(description, ' ', ' ', 'g') ILIKE '%The average processing%'
THEN 'Yes'
ELSE 'No'
END AS "SLA text"
FROM
test
I need to check if the field description
contains either of the following text:
- The average processing time for this type of request is 5 days.
- The average processing time for this type of request is 5 days. This
delay is reduced to 1 day for requests with high or critical
priority.
If either text is present, then mark the field (SLA text) as "Yes", otherwise mark it as "No".
If the field is marked as "Yes", then retrieve the integer value from the text.
For example, if the text is blabla The average processing time for this type of request is 5 days. blabla
, the value to be retrieved is 5
. If the text is The average processing time for this type of request is 5 days. This delay is reduced to 1 day for requests with high or critical priority.
, the value to be retrieved is 1/5
. This retrieved value should be stored in the days
column.
2
Answers
You can use
regex_replace
for this too:Here we replace your pattern with found digits from this pattern. And trim leading slash for case when second number is missing.
You can use substirng with regular expressions
fiddle