sqloracle-databaseoracle-sqldeveloperlongtext

Oracle SQL - Extracting <> tag values from LONG Type column

I have a table with column name "messages" of LONG TYPE The messages column contains data like 

<trans><body></body>...</trans>

I want to extract information inside the <body> tag only and select it. How would I get that information using an SQL query?

Solution

  • You can use SUBSTR() to extract that. Use the INSTR() function to figure out where the body tag starts, add 6 to compensate for the tag length. For the number of characters to go to, use another INSTR() to find the closing body tag, then subtract the open tag length.

    SELECT SUBSTR(my_column, INSTR(my_column,'<body>') + 6,
    INSTR(my_column,'</body>') - INSTR(my_column,'<body>') - 5) AS body_extracted
FROM my_table