Query to get specific patterns from column

Question

One of my table column contains the below value.

All Files (*.*)|*.*|Bitmap (*.bmp)|*.bmp|Microsoft Word Document
(*.docx)|*.docx|GIF (*.gif)|*.gif|JPEG (*.jpg)|*.jpg|PNG
(*.png)|*.png|Adobe Reader (*.pdf)|*.pdf|TIFF (*.tif)|*.tif

I need a query which will fetch the bmp,docx,gif,jpg,png,pdf,tif from above values. These values are present in brackets.

Need more info. What version of Oracle? Is your example 3 rows with columns separated by pipes, Or is the entire row as shown in one column? Could there be a null value in parentheses? Show an exact example of expected output. What have you tried so far? That may give a clue on what you are trying to do. — Gary_W, Jul 29 '15 at 14:58

score 0 · Accepted Answer · answered Jul 27 '15 at 13:12

0

Is this what you want ?

SELECT * 
FROM your_table 
WHERE column_with_types IN ('(.bmp)','(.docx)','(.gif)','(.jpg)','(.png)','(.pdf)','(.tif)')

answered Jul 27 '15 at 13:12

Pholochtairze

1,836
1
14
18

Gary_W · Answer 2 · 2015-07-29T16:02:05.333

Ok, given these assumptions:

Your sample data is 3 lines
The entire line is in one column
You don't want the *.* in the first line

Try this:

SQL> with tbl(line_nbr, str) as (
      select 1, 'All Files (*.*)|*.*|Bitmap (*.bmp)|*.bmp|Microsoft Word Document' from dual
      union
      select 2, '(*.docx)|*.docx|GIF (*.gif)|*.gif|JPEG (*.jpg)|*.jpg|PNG' from dual
      union
      select 3, '(*.png)|*.png|Adobe Reader (*.pdf)|*.pdf|TIFF (*.tif)|*.tif' from dual
    )
    SELECT line_nbr, str,
           COLUMN_VALUE AS match_nbr,
          REGEXP_SUBSTR( str ,'\(\*\.(\w*)\)', 1, COLUMN_VALUE, NULL, 1 ) AS match_value
   FROM   tbl,
          TABLE(
            CAST(
              MULTISET(
                SELECT LEVEL
                FROM   DUAL
                CONNECT BY LEVEL <= REGEXP_COUNT( str ,'\(\*\.\w*\)' )
              ) AS SYS.ODCINUMBERLIST
            )
          );

  LINE_NBR STR                                                               MATCH_NBR MATCH_VALUE
---------- ---------------------------------------------------------------- ---------- -----------
         1 All Files (*.*)|*.*|Bitmap (*.bmp)|*.bmp|Microsoft Word Document          1 bmp
         2 (*.docx)|*.docx|GIF (*.gif)|*.gif|JPEG (*.jpg)|*.jpg|PNG                  1 docx
         2 (*.docx)|*.docx|GIF (*.gif)|*.gif|JPEG (*.jpg)|*.jpg|PNG                  2 gif
         2 (*.docx)|*.docx|GIF (*.gif)|*.gif|JPEG (*.jpg)|*.jpg|PNG                  3 jpg
         3 (*.png)|*.png|Adobe Reader (*.pdf)|*.pdf|TIFF (*.tif)|*.tif               1 png
         3 (*.png)|*.png|Adobe Reader (*.pdf)|*.pdf|TIFF (*.tif)|*.tif               2 pdf
         3 (*.png)|*.png|Adobe Reader (*.pdf)|*.pdf|TIFF (*.tif)|*.tif               3 tif

7 rows selected.

SQL>

Thanks to MT0 in this post for the trick to properly handle mutiple rows: Split string by space and character as delimiter in Oracle with regexp_substr

Query to get specific patterns from column

2 Answers2

Linked