search all fields for string

[fuzzy1]

Hello All,

I have an email address entered wrong in some mystery field in the db, but have no idea where it is to correct. The only way I know about it is it comes back with bounced emails. Not sure if it is stuck in my CRM's cache, or in the db. Is there a way I can search all tables, all fields for the specific string???

[guelphdad]

I'd say you would have to search each table separately. If you don't then you would have to use a UNION and you would have to search the same number of fields in every table.

The good news is you CAN search all fields of a table if you know the fields.
Normally an IN list can be used to search multiple words from a single field like this:

select
foo,
bar,
qux
from yourtable
where qux in ('table','chair','sofa','television')

the good news is you can also do the inverse and search for a single term in multiple fields:

select
foo,
bar,
qux
from yourtable
where 'yoursearchterm' in (field1,field2,field3,field4)

the drawback of course is that it must be an exact match. If the email address you are searching for isn't the only item in the field (i.e. if it is part of some larger text), then you can only use LIKE and must name every field you are searching for.

[fuzzy1]

Thanks Much!
I had a feeling it was going to mean searching each table, but thought it wouldn't hurt to ask. I do appreciate the speedy response