Search for String Pattern in Netezza Database: Google Like Search

Have you ever wondered how to search for string pattern in Netezza database? Here is one of the method that I used in our organisation to search for string pattern in Netezza database. This is process will take bit time to search full Netezza database.

Here we have used the current_catalog to search for string pattern in current database. You can pass the database name and string pattern as a form parameter if you have any front end. You can build the query and get the desired results.

Read:

• Netezza Pivot Rows to Column With Example
• Netezza LEFT and RIGHT Functions
• IBM Netezza string Functions and Examples
• Netezza LIKE statement and Pattern matching Examples

Search for String Pattern in Netezza Database Example

So lets build query to search for string pattern in Netezza database:

select 'union all select '''||CURRENT_CATALOG||''' DB_NAME,'''||NAME||''' TABLE_NAME,'''||ATTNAME||''' COLUMN_NAME'
||',sum(sign(nvl(instr('||ATTNAME||',''my_search_string''),0))) ROWS_COUNT '
||' from '||CURRENT_CATALOG||'..'||NAME
from _v_relation_column
where OBJCLASS = 4905 --Instead of ID you can use NAME = 'TABLE' 
ORDER BY 1
;

This query will build you a query which will contain all tables in database. Note that this query will be as big as many tables in your database. So the execution will take time based on number of tables and volume of tables Netezza tables.  The output of above Netezza SQL query will looks something like one below:

https://gist.github.com/098c5f93a7ce49839773085c9ebc2ec4

Filter out the records ROWS_COUNT > 0. You will have your result:

TRAINING.ADMIN(ADMIN)-> ; 
 DB_NAME | TABLE_NAME | COLUMN_NAME | ROWS_COUNT 
----------+-------------+-------------+------------ 
 TRAINING | TEST_STRING | MY_STRING | 1 
(1 row)

Now let us check table content:

TRAINING.ADMIN(ADMIN)=> select * from TEST_STRING; 
 MY_STRING 
------------------ 
 my_search_string 
(1 row)