Extracting SLA information from PostgreSQL table with text matching and value extraction

executable
March 31, 2023
197 views
1 vote
2 Answers

I have the following postgresql :

CREATE TABLE test (
  id INT,
  description TEXT
);

INSERT INTO test VALUES 
(1, 'Some text'),
(2, '123 blabla The&nbsp;average processing time for this type of request is 5 days. blabla'),
(3, 'blalbla The&nbsp;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, '&nbsp;', ' ', '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.

Demo : https://www.db-fiddle.com/f/iNxLeZosApNzTyp9RNTK4r/1

Tags: postgresql sql

Answers

You can use regex_replace for this too:

select id, "SLA text",
    case when "SLA text" = 'Yes' then
        trim(leading '/' from regexp_replace(text,'(?:.*The average processing time for this type of request is (d+) days.)(?:
This delay is reduced to (d+) day for requests with high or critical priority.)?.*' , '2/1', 'g'))
    else '' end "SLA2 text"
from(
  SELECT 
      id,
      CASE 
          WHEN regexp_replace(description, '&nbsp;', ' ', 'g') ILIKE '%The average processing%' 
          THEN 'Yes'
          ELSE 'No'
      END AS "SLA text",
      regexp_replace(description, '&nbsp;', ' ', 'g') text
  FROM 
      test
) t

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

SELECT 
    id,
    CASE 
        WHEN regexp_replace(description, ' ', ' ', 'g') ILIKE '%The average processing%' 
        THEN 'Yes'
        ELSE 'No'
    END AS "SLA text",
  substring(description, '([0-9]*) day') days
FROM 
    test
where substring(description, '([0-9]*) day') IS NULL

id	SLA text	days
1	No	null

SELECT 1

SELECT 
    id,
    CASE 
        WHEN regexp_replace(description, ' ', ' ', 'g') ILIKE '%The average processing%' 
        THEN 'Yes'
        ELSE 'No'
    END AS "SLA text",
  substring(description, '([0-9]*) day') days
FROM 
    test
where substring(description, '([0-9]*) day') IS NULL
UNION ALL
select 
  id,
    MAX(CASE 
        WHEN regexp_replace(description, ' ', ' ', 'g') ILIKE '%The average processing%' 
        THEN 'Yes'
        ELSE 'No'
    END) AS "SLA text",  
  STRING_AGG(match[1], '/' ORDER BY match[1]) as days
from test
cross join lateral regexp_matches(description, '([0-9]*) day', 'g') as match
Group by id

id	SLA text	days
1	No	null
2	Yes	5
3	Yes	1/5
4	Yes	2/7
5	Yes	3

SELECT 5

fiddle

Please signup or login to give your own answer.

Click here to cancel reply.