CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle12csubstr
user12557498
user12557498

Reputation: 19

Take substring from a string dynamically in Oracle v11/v12

I have these strings and in Oracle I should take substrings dynamically.

Input:
Record: 11, Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso
Output:
Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso

Input:
Record: 3, Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso
Output:
Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso

Could you kindly help me with the query?

Thanks so much

Upvotes: 0

Views: 123

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

Considering that you need to extract the substring starting with the pattern Entità : upto the end of the line, use REGEXP_REPLACE() function such as

SELECT 'Entità : '||REGEXP_REPLACE(col,'(.*Entità : )(.*)','\2') AS output
  FROM t;

OUTPUT
----------------------------------------------------------------------------- 
Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso
Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso

Demo

Upvotes: 0

MT0
MT0

Reputation: 167981

Just use INSTR to find the index of the first comma and then take the substring starting 2 characters later (to skip the comma and white space):

SELECT SUBSTR( input, INSTR( input, ',' ) + 2 ) AS output
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name ( input ) AS
SELECT 'Record: 11, Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso' FROM DUAL UNION ALL
SELECT 'Record: 3, Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso' FROM DUAL;

Outputs:

| OUTPUT                                                                         |
| :----------------------------------------------------------------------------- |
| Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso |
| Entità : CustomerOrder, attributo: DESCRIPTION non conforme al formato atteso |

db<>fiddle here

Upvotes: 1

Related Questions