Search code examples
sqlfirebird

Firebird: Using SQL to search all fields?

I have a SQL query that is going to be used to search fields that are most likely going to change a lot. I.e More fields will be added. How can I write a sql query using a simple like that will search across al fields without explicitly specifying the fields?

Something like: 

select * from MYTABLE where CONCAT(*) like '%mySearchTerm%';

Is there an easy way to do this?

Solution

  • I think the only way in Firebird is to use calculated field which concats the fields you want to search:

    create table T (
  foo varchar(24) not null,
  bar varchar(24) not null,
  ...
  SearchField varchar(1024) generated always as (foo || '##' || bar)
)

select * from T where SearchField like '%mySearchTerm%';

    So each time youre altering the table (adding or dropping an field) you also have to change the calculated field, but the query would remain the same. But this has some impact on perfomance as you're doing concat you really don't need...