sql - delete from the request oracle all that is further words "info"

Question

I have an oracle request.

    SELECT DISTINCT  
           to_char(to_date('1970/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss') + rep.value_number/86400000, 'yyyy.mm.dd hh24:mi:ss') ||' '|| nvl(trns.representation, '')  transaction_number 
    FROM IOT_STREAM_ANALYTICS_LOG_REPRESENTATION rep
        LEFT join txn 
       on rep.VALUE_NUMBER = txn.transaction_value_number
        
        LEFT  JOIN IOT_STREAM_ANALYTICS_EVENT_LOG evlog
           ON rep.parent = evlog.id
         LEFT JOIN IOT_DEVICE_EVENT ev
           ON evlog.device_event = ev.id
         LEFT JOIN IOT_STREAM_ANALYTICS_TRANSACTIONS trns
           ON rep.value_number = trns.name
    WHERE lower(rep.key) = 'transaction'
          AND rep.value_number <> 0
          AND rownum = 1 

This query is easy I am looking for a deadline with some information. This query works, but I want to remove something from the answer. The answer I have is one template:

2021.01.25 13:48:15 weight: 0 max weight: 35000 min weight: 1000 info: 37

I want to remove the word INFO from this answer and everything after that word. I will be grateful for your help!

question from:https://stackoverflow.com/questions/65888674/delete-from-the-request-oracle-all-that-is-further-words-info

Answer 1

You would use regexp_substr function like below

regexp_substr(your_answer_column, '(.*?)info.*?$', 1, 1, 'i', 1)