Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Substring from Clob or Blob column

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
raana jella
Asked:
raana jella
  • 2
2 Solutions
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
raana jellaAuthor Commented:
Thank you,
I will try these solutions and will let you know
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now