1 2 3 Previous Next 34 Replies Latest reply: Dec 27, 2013 1:17 PM by Xev Bellringer RSS

How can I use SQL to search for a pattern within a field?

Xev Bellringer Explorer
Currently Being Moderated

Hello, Frank, Solomon, ect

 

I am now faced with this particular scenario, I've got the SQL to search through a field to find text within the field, but I have to know what it is before it can look for it.

What I have to do is this:

 

Search through a field, for a pattern, and I won't know what the data is I am looking for. Can this be done in SQL?

 

For instance, Here is my SQL this far, I was helped allot in order to get to this point.

 

select table_name,

       column_name,

       :search_string search_string,

       result

  from (select column_name,

               table_name,

               'ora:view("' || table_name || '")/ROW/' || column_name || '[ora:contains(text(),"%' || :search_string || '%") > 0]' str

          from cols

         where table_name in ('TABLE1', 'TABLE2')),

       xmltable (str columns result varchar2(10) path '.')

 

 

When you execute the above SQL, you have to pass in a value. What I really need is to alter the above SQL, to make it search for a pattern that exist's within the text of the field itself.

Like for instance, lets say the pattern I am looking for is this" xx-xxxxx-xxxx" and it's somewhere in a field.

 

I need to alter this SQL to take this pattern and search through all the schemas and tables to look for this pattern match.

 

Can be done?

  • 1. Re: How can I use SQL to search for a pattern within a field?
    Xev Bellringer Explorer
    Currently Being Moderated

    Guys, I actually want to use dbms_xmlgen.getxmltype

     

    So the SQL I am going to use is this: This currently will find every single instance of one value that I pass in, but I need to search for a pattern like this for example: "xxxxx-xx-xxx-xxx"

    and other patterns also.

     

    This is the SQL I am going to use:

    var val varchar2(5)

    exec :val := 'as'

     

     

    select distinct substr (:val, 1, 11) "Searchword",

                    substr (table_name, 1, 14) "Table",

                    substr (t.column_value.getstringval (), 1, 50) "Column/Value"

               from cols,

                    table

                       (xmlsequence

                           (dbms_xmlgen.getxmltype ('select ' || column_name

                                                    || ' from ' || table_name

                                                    || ' where upper('

                                                    || column_name

                                                    || ') like upper(''%' || :val

                                                    || '%'')'

                                                   ).extract ('ROWSET/ROW/*')

                           )

                       ) t

            where table_name in ('TABLE1') --, 'JOB_HISTORY', 'DEPARTMENTS')

           order by "Table"

  • 2. Re: How can I use SQL to search for a pattern within a field?
    Xev Bellringer Explorer
    Currently Being Moderated

    Another problem I am having is that when I execute this SQL, and try to find all the tables, it only  comes

    back with the one table I have created in my personal schema, not the other schema's like it needs to.

     

    In order words, when I run this: select distinct table_name from cols; I get only one table back, how do I check for all the other tables in their own schema's?

  • 3. Re: How can I use SQL to search for a pattern within a field?
    Xev Bellringer Explorer
    Currently Being Moderated

    Guys, I made a alteration in the SQL! I figured out how to find the other tables in the other schemas!!!!

     

    Here is the altered SQL that searches through all the tables in other schema's!!!

     

    Now, all I need it to figure out how to search for a "pattern" like this: xxx-xxxx-xxxx and so on...

     

    Here is the modified SQL:

     

     

    select table_name,

           column_name,

           :search_string search_string,

           result

      from (select column_name,

                   table_name,

                   'ora:view("' || table_name || '")/ROW/' || column_name || '[ora:contains(text(),"%' || :search_string || '%") > 0]' str

              from dba_tab_cols

             where table_name in ('A_TABLE', 'B_TABLE')),

           xmltable (str columns result varchar2(10) path '.')

        

    I am using "dba_tab_cols" instead of the public synonym "cols"  that way I can see the other tables!!!!

     

    BUT, the only problem is that when I used dba_tab_cols, its says the "table or view does not exist"

     

    Geez, not sure what to do at this point?

  • 4. Re: How can I use SQL to search for a pattern within a field?
    Xev Bellringer Explorer
    Currently Being Moderated

    It seems that the public synonym cannot see all the other tables, it just see my one table in my schema? Why is that?

    When I do a select on "dba_tab_cols" it see all the other tables in other schema's, but when i try to use dba_tab_cols in the above SQ code, it says to me that "table or view does not exist?

  • 5. Re: How can I use SQL to search for a pattern within a field?
    Martin Preiss Expert
    Currently Being Moderated

    cols is a synonym for USER_TAB_COLUMNS:

    SQL> select * from dba_synonyms where synonym_name = 'COLS';

     

    OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME

    ------------------------------ ------------------------------ ------------------------------ ----------------

    PUBLIC                         COLS                           SYS                            USER_TAB_COLUMNS

    How did you grant the access on dba_tab_cols to your user? (direct select grant on dba_tab_cols, or a role: dba or select_catalog_role, or?)

  • 6. Re: How can I use SQL to search for a pattern within a field?
    Xev Bellringer Explorer
    Currently Being Moderated

    Hi Martin

     

    When I run this query, this is what I get.

     

    OWNER                          SYNONYM_NAME                           TABLE_OWNER                    TABLE_NAME                     DB_LINK                                                                                                                    

    ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------

    PUBLIC                         COLS                                                SYS                                       USER_TAB_COLUMNS                                                                                                                                            

    However, the user I am allowed to use, is not a dba role. I need to be able to see all those other tables and schema's with the synonym col, or it won't work.

    is there anyway around using it??

     

    I can select on 'select column_name, table_name from dba_tab_cols;' with no problem, it's just when I try to use dba_tab_cols, with the above code, it doesn't work....but it works with the public synonym "cols"

    Isn't that weird??

     

    also, it cols, doesn't see all those other tables....that's the main issue...

     

     

    Ok, I have been playing around with REGEXP_LIKE and I have the SQl that I need to run to find the pattern I was looking for....

     

    Here is the SQL I am going to run:

     

     

     

    SELECT find_pat

    from find_pat_test

    WHERE REGEXP_LIKE(find_pat,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$');

     

    NOW, How do I incorporate this into the above SQL where it checks all the tables in all the fields in all the schema's for this SQL right here?

    ----------------------------------------------------------------------------------------------------

    SELECT find_pat

    from find_pat_test

    WHERE REGEXP_LIKE(find_pat,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$');

    -----------------------------------------------------------------------------------------------------

  • 7. Re: How can I use SQL to search for a pattern within a field?
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    When you use something dynamically within a function or procedure, roles do not apply and privileges must be granted directly.  So, you need to grant select on dba_tab_cols directly.  If you want to do pattern matching then you should use regular expressions.  The following example grants the proper privileges and uses regexp_instr to find all values containing the pattern xxx-xxxx-xxxx, where /S is used for any non-space character.  I limited the tables in order to save time and output for the test, but you can eliminate that where clause.

     

     

    SYS@orcl> CREATE USER test IDENTIFIED BY test

      2  /

     

    User created.

     

    SYS@orcl> ALTER USER test QUOTA UNLIMITED ON USERS

      2  /

     

    User altered.

     

    SYS@orcl> GRANT CREATE SESSION, CREATE TABLE TO test

      2  /

     

    Grant succeeded.

     

    SYS@orcl> GRANT SELECT ON dba_tab_cols TO test

      2  /

     

    Grant succeeded.

     

    SYS@orcl> CONNECT test/test

    Connected.

    TEST@orcl> SET LINESIZE 90

    TEST@orcl> CREATE TABLE table1

      2    (tab1_col1  VARCHAR2(60))

      3  /

     

    Table created.

     

    TEST@orcl> INSERT ALL

      2  INTO table1 (tab1_col1) VALUES ('xxx-xxxx-xxxx')

      3  INTO table1 (tab1_col1) VALUES ('matching abc-defg-hijk data')

      4  INTO table1 (tab1_col1) VALUES ('other data')

      5  SELECT * FROM DUAL

      6  /

     

    3 rows created.

     

    TEST@orcl> CREATE TABLE table2

      2    (tab2_col2  VARCHAR2(30))

      3  /

     

    Table created.

     

    TEST@orcl> INSERT ALL

      2  INTO table2 (tab2_col2) VALUES ('this BCD-EFGH-IJKL too')

      3  INTO table2 (tab2_col2) VALUES ('something else')

      4  SELECT * FROM DUAL

      5  /

     

    2 rows created.

     

    TEST@orcl> VAR search_string VARCHAR2(24)

    TEST@orcl> EXEC :search_string := '\S\S\S-\S\S\S\S-\S\S\S\S'

     

    PL/SQL procedure successfully completed.

     

    TEST@orcl> COLUMN "Searchword"     FORMAT A24

    TEST@orcl> COLUMN "Table"     FORMAT A6

    TEST@orcl> COLUMN "Column/Value" FORMAT A50

    TEST@orcl> SELECT DISTINCT SUBSTR (:search_string, 1, 24) "Searchword",

      2               SUBSTR (table_name, 1, 14) "Table",

      3               SUBSTR (t.column_value.getstringval (), 1, 50) "Column/Value"

      4  FROM   dba_tab_cols,

      5          TABLE

      6            (XMLSEQUENCE

      7           (DBMS_XMLGEN.GETXMLTYPE

      8              ( 'SELECT ' || column_name ||

      9               ' FROM ' || table_name ||

    10               ' WHERE REGEXP_INSTR

    11                     (UPPER (' || column_name || '),''' ||

    12                  UPPER (:search_string) || ''') > 0'

    13              ).extract ('ROWSET/ROW/*'))) t

    14  WHERE  table_name IN ('TABLE1', 'TABLE2')

    15  ORDER  BY "Table"

    16  /

     

    Searchword               Table  Column/Value

    ------------------------ ------ --------------------------------------------------

    \S\S\S-\S\S\S\S-\S\S\S\S TABLE1 <TAB1_COL1>matching abc-defg-hijk data</TAB1_COL1>

    \S\S\S-\S\S\S\S-\S\S\S\S TABLE1 <TAB1_COL1>xxx-xxxx-xxxx</TAB1_COL1>

    \S\S\S-\S\S\S\S-\S\S\S\S TABLE2 <TAB2_COL2>this BCD-EFGH-IJKL too</TAB2_COL2>

     

    3 rows selected.

  • 8. Re: How can I use SQL to search for a pattern within a field?
    Martin Preiss Expert
    Currently Being Moderated

    Hello Barbara,

     

    that's a nice demo and is exactly what I was thinking about, when I asked my questions about the way the access was granted to the user. But when I use your example, revoke the select on dba_tab_cols, and grant the role select_catalog_role to (and enable it for) test1 the user still can access dba_tab_cols with the DBMS_XMLGEN.GETXMLTYPE query. But at the moment I don't find the time to analyze the behaviour more thoroughly.

     

    Regards

     

    Martin Preiss

  • 9. Re: How can I use SQL to search for a pattern within a field?
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    I tested and confirmed the same behavior.  It looks like dbms_xmlgen.getxmltype must be created in some manner such that select on dba_tab_cols through a role is sufficient.  However, if I create a simple procedure as user test that selects from dba_tab_cols, then that role is not sufficient and a direct grant is required.  I am having difficulty envisioning how dbms_xmlgen.xmltye is created.  If it were created with system privileges, then the user running it would not need select on dba_tab_cols at all, but that is required one way or another.  Unfortunately, we can't see the source code.

  • 10. Re: How can I use SQL to search for a pattern within a field?
    Randolf Geist Oracle ACE Director
    Currently Being Moderated

    Martin Preiss wrote:

     

    But when I use your example, revoke the select on dba_tab_cols, and grant the role select_catalog_role to (and enable it for) test1 the user still can access dba_tab_cols with the DBMS_XMLGEN.GETXMLTYPE query.

    I think the simplest explanation is that DBMS_XMLGEN is using invoker's rights (AUTHID CURRENT_USER) and hence privileges granted via roles still apply.

     

    But I see a completely different problem with the samples posted by the OP above: The "table or view does not exist" error very likely comes from the fact that the samples posted so far don't reference the object to search through fully qualified with the owner - so any object outside the current user won't be resolved.

     

    Solution: Add the "OWNER" to the corresponding dynamically generated SQL and things should be fine (except for table names that screw the "ora:view" expression, like "COL$" :-), so it's probably more reliable to use DBMS_XMLGEN instead.

     

    For example:

     

    select table_name,

           column_name,

           :search_string search_string,

           result

      from (select column_name,

                   table_name,

                   'ora:view("' || owner || '", "' || table_name || '")/ROW/' || column_name || '[ora:contains(text(),"%' || :search_string || '%") > 0]' str

              from dba_tab_cols

             where table_name in ('COUNTRIES', 'DEPARTMENTS')),

           xmltable (str columns result varchar2(10) path '.');

     

     

    And it makes sense to limit the search in DBA_TAB_COLS also to the corresponding schemas... which I haven't done above.

     

    Randolf

  • 11. Re: How can I use SQL to search for a pattern within a field?
    Xev Bellringer Explorer
    Currently Being Moderated

    Ok, I think I got the idea now...

     

    Searchword               Table          Column/Value                                

    ------------------------ -------------- --------------------------------------------------

    \S\S\S-\S\S\S\S-\S\S\S\S BARB_TABLE1    <TAB1_COL1>matching abc-defg-hijk data</TAB1_COL1>

    \S\S\S-\S\S\S\S-\S\S\S\S BARB_TABLE1    <TAB1_COL1>xxx-xxxx-xxxx</TAB1_COL1>          

    \S\S\S-\S\S\S\S-\S\S\S\S BARB_TABLE2    <TAB2_COL2>this BCD-EFGH-IJKL too</TAB2_COL2> 

     

     

    ````````````````````````````````Ok, this is exactly what I need! it worked! Thank you so much Barbara , You're wonderful!````````````````````````````````````````````

     

     

    Either way will work for me!!!

     

    SELECT find_col

    from find_test

    WHERE REGEXP_LIKE(find_col,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$');

     

    thank you

  • 12. Re: How can I use SQL to search for a pattern within a field?
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    SYS@orcl> CREATE USER test IDENTIFIED BY test

      2  /

     

    User created.

     

    SYS@orcl> ALTER USER test QUOTA UNLIMITED ON USERS

      2  /

     

    User altered.

     

    SYS@orcl> GRANT CREATE SESSION, CREATE TABLE TO test

      2  /

     

    Grant succeeded.

     

    SYS@orcl> GRANT SELECT ON dba_tab_cols TO test

      2  /

     

    Grant succeeded.

     

    SYS@orcl> CONNECT test/test

    Connected.

    TEST@orcl> SET LINESIZE 90

    TEST@orcl> CREATE TABLE find_test

      2    (find_col  VARCHAR2(60))

      3  /

     

    Table created.

     

    TEST@orcl> INSERT ALL

      2  INTO find_test (find_col) VALUES ('111-22-3333')

      3  INTO find_test (find_col) VALUES ('111=22-3333')

      4  INTO find_test (find_col) VALUES ('111-A2-3333')

      5  INTO find_test (find_col) VALUES ('111-22-33339')

      6  INTO find_test (find_col) VALUES ('111-2-23333')

      7  INTO find_test (find_col) VALUES ('777-77-7777')

      8  SELECT * FROM DUAL

      9  /

     

    6 rows created.

     

    TEST@orcl> VAR search_string VARCHAR2(28)

    TEST@orcl> EXEC :search_string := '^[0-9]{3}-[0-9]{2}-[0-9]{4}$'

     

    PL/SQL procedure successfully completed.

     

    TEST@orcl> COLUMN "Searchword"     FORMAT A28

    TEST@orcl> COLUMN "Table"     FORMAT A9

    TEST@orcl> COLUMN "Column/Value" FORMAT A50

    TEST@orcl> SELECT DISTINCT SUBSTR (:search_string, 1, 28) "Searchword",

      2               SUBSTR (table_name, 1, 14) "Table",

      3               SUBSTR (t.column_value.getstringval (), 1, 50) "Column/Value"

      4  FROM   dba_tab_cols,

      5          TABLE

      6            (XMLSEQUENCE

      7           (DBMS_XMLGEN.GETXMLTYPE

      8              ( 'SELECT ' || column_name ||

      9               ' FROM ' || table_name ||

    10               ' WHERE REGEXP_LIKE

    11                     (' || column_name || ','''

    12                    || :search_string || ''')'

    13              ).extract ('ROWSET/ROW/*'))) t

    14  WHERE  table_name IN ('FIND_TEST')

    15  ORDER  BY "Table"

    16  /

     

    Searchword                   Table     Column/Value

    ---------------------------- --------- --------------------------------------------------

    ^[0-9]{3}-[0-9]{2}-[0-9]{4}$ FIND_TEST <FIND_COL>777-77-7777</FIND_COL>

    ^[0-9]{3}-[0-9]{2}-[0-9]{4}$ FIND_TEST <FIND_COL>111-22-3333</FIND_COL>

     

    2 rows selected.

  • 13. Re: How can I use SQL to search for a pattern within a field?
    Xev Bellringer Explorer
    Currently Being Moderated

    Barbara,

     

    Can I make this line  `````WHERE  table_name IN ('FIND_TEST')```````` a sub-query so it will look in all the tables???

  • 14. Re: How can I use SQL to search for a pattern within a field?
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    You can make it a sub-query that selects whatever tables you want:

     

    WHERE table_name IN (SELECT ...)

     

    or just remove that WHERE clause to select all tables.  If you have any table names or column names that are created in mixed case or starting with numbers of anything strange, then you may need to add double quotes around them like so (not tested):

     

     

    SELECT DISTINCT SUBSTR (:search_string, 1, 28) "Searchword",

                    SUBSTR (table_name, 1, 14) "Table",

                    SUBSTR (t.column_value.getstringval (), 1, 50) "Column/Value"

    FROM   dba_tab_cols,

           TABLE

             (XMLSEQUENCE

                (DBMS_XMLGEN.GETXMLTYPE

                   ( 'SELECT "' || column_name ||

                    '" FROM "' || table_name ||

                    '" WHERE REGEXP_LIKE

                              ("' || column_name || '",'''

                                 || :search_string || ''')'

                   ).extract ('ROWSET/ROW/*'))) t

    ORDER  BY "Table"

    /

1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points