CODEWITHSUNDEEP
sqlselectfirebird
Danilo
Danilo

Reputation: 2036

Search and find a string value in all columns

I need to find if a string value exists in any column in my table. It's possible create automatically procedure or query in Firebird that perform this work without making explicit column names?

Column names can be find with this query:

select RDB$RELATION_FIELDS.rdb$field_name as NC from RDB$RELATION_FIELDS where RDB$RELATION_FIELDS.rdb$relation_name = MY_Table_name

I would use a procedure similar to that in Firebird:

http://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/

SET TERM ^^ ;

CREATE PROCEDURE P_FINDSTRINGINTABLE (
  STRTOFIND VarChar(255), 
  TBLNAME VarChar(255))
 returns (
  SQLCOMMAND VarChar(25000))
AS
DECLARE VARIABLE condSTR VARCHAR(8000);
DECLARE VARIABLE columnName VARCHAR(8000);
begin
  sqlCommand = 'select * from '|| upper( :TBLNAME );
  
  condSTR = '';
  for select RDB$RELATION_FIELDS.rdb$field_name as NC from RDB$RELATION_FIELDS where RDB$RELATION_FIELDS.rdb$relation_name = upper( :TBLNAME )
  into  :columnName  
  do begin
     condSTR = condSTR||columnName||' LIKE ''%'||STRTOFIND||'%'' OR ';   
  end
         
  
  
  IF ( CHAR_LENGTH(condSTR) > 0 ) THEN 
  begin
     condSTR = ' where ' || left(condSTR, CHAR_LENGTH (condSTR)-3)  || ';';
     sqlCommand = sqlCommand|| condSTR;  
  end
  ELSE sqlCommand = sqlCommand||';' ;

  
end ^^


SET TERM ; ^^

I've created the right string SQL for my search. Now, if I execute this Firebird procedure, it returns the SQL command as string.

Are there methods to automatically execute the returned select?

Upvotes: 3

Views: 7355

Answers (3)

Nata
Nata

Reputation: 1164

Here is the syntax, based on the above posts, to simply run a SELECT statement, without creating a stored procedure:

SET TERM ^ ;
EXECUTE BLOCK 
RETURNS
( table_with_value varchar(200),
    column_with_value varchar(200),
    containing_value varchar(4000))
AS
declare variable all_tables varchar(200);
declare variable all_columns varchar(200);
declare variable all_values varchar(4000);
BEGIN
    FOR SELECT
        r.rdb$relation_name, f.rdb$field_name
        from rdb$relation_fields f
        JOIN RDB$FIELDS ON (RDB$FIELDS.RDB$FIELD_NAME = f.RDB$FIELD_SOURCE)
        join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name
        and r.rdb$view_blr is null 
        and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
        and RDB$FIELDS.RDB$FIELD_TYPE in (37, 40)
        and RDB$FIELDS.RDB$FIELD_TYPE is not null
        order by 1, f.rdb$field_position INTO :all_tables, :all_columns
    DO
    BEGIN
        FOR EXECUTE STATEMENT 'SELECT "' || all_columns || '" FROM "' || all_tables || '"'
            INTO :all_values
        DO
        BEGIN
            IF (all_Values like '%SEARCH_VALUE%') THEN
            BEGIN
                table_With_Value = all_Tables;
                column_With_Value = all_Columns;
                containing_value = all_Values;
                SUSPEND;
           END
        END
    END
END^
SET TERM ^ ;

Upvotes: 1

Raphael Neve
Raphael Neve

Reputation: 26

You can achieve something quite close to what you want, I believe, by using the EXECUTE STATEMENT to create a view that would then return the values.

Example of what I mean:

sqlCommand = 'select * from '|| upper( :TBLNAME );

/* Rest of your code for creating the sqlCommand variable */

viewCommand = "RECREATE VIEW V_SEARCH_RESULTS AS " || sqlCommand;

EXECUTE STATEMENT viewCommand;

So basically your procedure P_FINDSTRINGINTABLE would NOT return any values, but would (re)create a view dynamically called V_SEARCH_RESULTS. All you would then have to do is select from this view to see the results.

Upvotes: 1

Marcodor
Marcodor

Reputation: 5741

If you need full text search in your DB, I suggest you to use dedicated search engine that integrates with Firebird. This is for performance reasons. Take a look at SphinxSearch or Solr engines.

Upvotes: 1

Related Questions