Re: [sqr-users] How to find a characters string in a table

["Larry Roux" <LRoux@syr.edu>]

You might be able to create a select statement against the DB table/column list 
which creates a dynamic select statement that strings the fields together.

The following is not real code as I forget the tables that exist in the db for 
this, but here is the idea:

begin-procedure create-select
Let $fields = ''
begin-select
v$syscolumns.columnname
  Let $fields = $fields || ' || '  || &v$syscolumns.columnname
from v$syscolumns
where tablename = 'MYTABLE'
end-select
Do do-the-select
end-procedure

begin-procedure do-the-select
begin-select
$fields &ggg
  show 'string found in ' &ggg
from MYTABLE
where $fields like '%srchstring%'
end-select
end-procedure

or something like that.  It's probably not efficient....and I don't think it 
would work in Oracle if there is a LONG field on the table.  

Just an idea that might trigger something on your side that will work.




******************************
Larry Roux
Syracuse University
lroux@syr.edu
*******************************

>>> Charles-Henri.Faure@tamoil.ch 11/27/03 09:34AM >>>
Thanks,

The solution you propose is exactly what I wanted to avoid ! I must be 
more patient.

Best regards.

Charles-Henri Faure




"George Jansen" <gjansen@aflcio.org> 
Envoyé par : sqr-users-admin@sqrug.org 
27.11.2003 15:16
Veuillez répondre à
sqr-users@sqrug.org 


A
<sqr-users@sqrug.org>
cc

Objet
Re: [sqr-users] How to find a characters string in a table






Column by column! Check the data dictionary for the
columns that are of a character data type and build your query
dynamically.

For an arbitrary table this would be a LOT easier in something like Perl
or (in Oracle) PL/SQL than in SQR to do in (kind of) one pass. Me, I'd
use Perl: build my list of columns to check, then use a fetchrow_hashref
to make it trivial to say in what column I found the string.  But
perhaps the Ontko gang are wizardly enough to do this for the general
case in one SQR program.

For a single table of known structure there isn't much to this but
typing the nested if/else ifs and using instr.

You wrote:

>>>>>>

I would like to search for a specific character string in a table
without 
specifying the column name. How can I do ?

Thanks for your help.


Charles-Henri Faure

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    DISCLAIMER 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This E-mail is private and confidential and may be covered by legal
professional privilege. It is therefore only intended for the
addressee(s). If you are not
one of those persons you must not read, copy, store, disclose, use or
distribute its contents. Please also contact the sender immediately and
delete the original.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    AVERTISSEMENT 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Ce Courrier electronique est prive et confidentiel et peut etre
considere comme document professionnel legal. Il est donc uniquement
destine aux personnes
concernees. Si vous n'etes pas l'une de ces personnes vous ne devez pas
lire, copier, stocker, reveler, employer ou distribuer son contenu. Si
tel devait etre
la cas veuillez prendre immediatement contact avec l'expediteur et
supprimer l'original.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users