查询以从列中获取特定模式 [英] Query to get specific patterns from column

问题描述

我的表格列之一包含以下值.

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

我需要一个查询，该查询将从上述值中获取bmp,docx,gif,jpg,png,pdf,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.

推荐答案

确定，假设以下条件:

• 您的样本数据是3行
• 整行在一栏中
• 您不希望第一行中的*.*

尝试一下:

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>

在此文章中，感谢MT0提供了正确处理多个行的技巧:

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

这篇关于查询以从列中获取特定模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！