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

/Franz

asked 05 Apr, 07:12

Franz_Stf's gravatar image

Franz_Stf
103158
accept rate: 0%

edited 05 Apr, 09:04

Volker%20Barth's gravatar image

Volker Barth
34.7k337491732

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

/Franz

(05 Apr, 07:24) Franz_Stf

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
...
permanent link

answered 05 Apr, 09:18

Breck%20Carter's gravatar image

Breck Carter
29.4k487653960
accept rate: 20%

edited 05 Apr, 09:22

Ok, thank you very much. /Franz

(06 Apr, 06:55) Franz_Stf

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

permanent link

answered 08 Apr, 05:14

Frank's gravatar image

Frank
314111323
accept rate: 33%

edited 08 Apr, 05:21

Probably not what you want, but...

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

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

...or, Full Text Search.

permanent link

answered 05 Apr, 07:21

Breck%20Carter's gravatar image

Breck Carter
29.4k487653960
accept rate: 20%

edited 05 Apr, 07:23

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×4

question asked: 05 Apr, 07:12

question was seen: 289 times

last updated: 08 Apr, 05:21