Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts

    search all fields for string

    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???

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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:

    Code:
    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:

    Code:
    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.

  • #3
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •