Solved

Substring from Clob or Blob column

Posted on 2014-09-08
3
385 Views
Last Modified: 2014-09-15
I have a table with blob column which stores all comments entered by users for particular assest (asset01)

CommentHeaderuser1 CommentDate2014-08-15-14.48.14.000000
CommentDateStageNbr20StageNbrCommentHeaderCommentBodytest 1 - first test CommentBody

CommentHeaderuser2 CommentDate2014-08-15-14.13.08.000000
CommentDateStageNbr20StageNbrCommentHeaderCommentBodyTest 2 - Second TestCommentBody

User 1 entered comments test 1 - first test which is stored between commentbody and commentbody
User 2 eneterd Test 2 - Second Test which is stored between commentbody
How to query using sql to get these comments as seperate lines
Line 1 - Test 1 - FirstTest
Line 2 - Test 2 - Second Test


Table contains multiple assets and each asset will have comments stored in tags
0
Comment
Question by:raana jella
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40311278
SELECT REPLACE(
           REGEXP_SUBSTR(
               comments,
               'CommentBody.+CommentBody',
               1,
               x.COLUMN_VALUE
           ),
           'CommentBody'
       )
  FROM yourtable,
       TABLE(
           (SELECT COLLECT(LEVEL)
              FROM DUAL
            CONNECT BY LEVEL <= REGEXP_COUNT(yourtable.comments, 'CommentBody.+CommentBody'))
       ) x
 WHERE asset = 'asset01'
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 40311282
and another version that doesn't require regular expressions, but same basic idea


SELECT asset, SUBSTR(comments, s, e - s)
  FROM (SELECT asset,
               comments,
                 INSTR(
                     comments,
                     'CommentBody',
                     1,
                     COLUMN_VALUE * 2 - 1
                 )
               + LENGTH('CommentBody')
                   s,
               INSTR(
                   comments,
                   'CommentBody',
                   1,
                   COLUMN_VALUE * 2
               )
                   e
          FROM yourtable,
               TABLE(
                   (SELECT COLLECT(LEVEL)
                      FROM DUAL
                    CONNECT BY INSTR(
                                   comments,
                                   'CommentBody',
                                   1,
                                   LEVEL * 2
                               ) != 0)
               ) x
         WHERE asset = 'asset01');
0
 

Author Comment

by:raana jella
ID: 40312030
Thank you,
I will try these solutions and will let you know
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question