Search in a record/row

[former_SQLA_member1694897]

Hi all Is there a SQL command that searches for a search term in a record? (not in a single field)

/Franz

[former_SQLA_member1694897]

i mean: WHERE STRING ( Record ) LIKE '%whatever'

/Franz

[fvestjens]

I guess this would work. But maybe you have to tweak it a little bit to match your needs

create or replace function usr.RowContains(in in_TableName varchar(64),in in_RecordId integer,in in_SearchFor varchar(256))
returns integer
begin
  declare l_ColumnList long varchar;
  declare l_Result integer;
  //
  execute immediate
    'select list(column_name) into l_ColumnList '||
    'from systables_view Tbl join syscolumn_view Col on Col.table_Id = Tbl.table_id '||
    'where table_name = '''|| in_TableName ||'''';
  //
  execute immediate
    'select (if exists(select 1 from '|| in_TableName ||' where Id = '|| in_RecordId ||' and String('|| l_ColumnList ||') like ''%'|| in_SearchFor ||'%'') then 1 else 0 endif) into l_Result';
  //
  return l_Result
end

[Breck_Carter]

There is no scalar function like STRING ( record ), but there is an UNLOAD INTO VARIABLE, so depending on how badly you want to do this, the following code lets you search "SELECT *" without naming every column...

(note that it does not name the column proc_defn which is where the LIKE is finding its matches)

BEGIN
DECLARE @rowstring LONG VARCHAR;
FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT SYSPROCEDURE.proc_id   AS @proc_id,
       SYSPROCEDURE.proc_name AS @proc_name
  FROM SYSPROCEDURE
 ORDER BY SYSPROCEDURE.proc_id
FOR READ ONLY
DO
   UNLOAD 
   SELECT * 
     FROM SYSPROCEDURE 
    WHERE proc_id = @proc_id 
     INTO VARIABLE @rowstring;
   IF @rowstring LIKE '%systab%' THEN
      MESSAGE STRING ( @proc_name ) TO CONSOLE;
   END IF;
END FOR;
END;

sa_conn_info
sp_objectpermission
sp_columns
sp_fkeys
sp_pkeys
sp_special_columns
sp_statistics
col_length
index_col
sa_validate
sa_table_stats
...

[former_SQLA_member1694897]

Ok, thank you very much. /Franz

[Breck_Carter]

Probably not what you want, but...

WHERE col1 LIKE '%whatever%' OR col2 LIKE '%whatever'

WHERE STRING ( col1, col2 ) LIKE '%whatever'

...or, Full Text Search.