6 Replies - 5743 Views - Last Post: 30 November 2012 - 10:46 AM

#1 skyshare  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-November 12

Search whole table

Posted 30 November 2012 - 04:50 AM

I have an access table with 20 fields with a VB6 connection. Is there a way in sql to search all fields in the table for an item of text without referencing each field individually?


I am trying to avoid the following...

Select * from <tablename> where field1 ='text' or field2 = 'text' or field3='text'....etc


thanks
Is This A Good Question/Topic? 0
  • +

Replies To: Search whole table

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8396
  • View blog
  • Posts: 31,231
  • Joined: 12-June 08

Re: Search whole table

Posted 30 November 2012 - 08:09 AM

Yes, you'll need to name all the columns. It wouldn't make sense to search a text value across say the row id, right? I am curious what forty columns make up this table and under what scenerio a text seach would apply to all of them. Maybe a re-architecture is called for.
Was This Post Helpful? 0
  • +
  • -

#3 skyshare  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-November 12

Re: Search whole table

Posted 30 November 2012 - 09:57 AM

The fields are text fields named 'PR1' to 'PR20' which hold a string of text less than 10 characters. Not all are populated but there are thousands of rows. The text I want to search for could be anywhere in the table.

So the current sql I use would be :
'select * from mytable where PR1 = 'mytext' or PR2 = 'mytext' or pr3 = 'mytext' ....etc

I just wondered if there was a way to search the whole table at once?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8396
  • View blog
  • Posts: 31,231
  • Joined: 12-June 08

Re: Search whole table

Posted 30 November 2012 - 10:01 AM

So there's no row index? No date entered? Nothing that *wouldn't* be searched?
Was This Post Helpful? 0
  • +
  • -

#5 skyshare  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-November 12

Re: Search whole table

Posted 30 November 2012 - 10:05 AM

No, no other fields apart from the index
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8396
  • View blog
  • Posts: 31,231
  • Joined: 12-June 08

Re: Search whole table

Posted 30 November 2012 - 10:10 AM

There's your odd case out.. a column that *doesn't* need to be searched.. so yes.. enumerate all your columns.. then that also helps future proof against forgetfulness. Say you add a new column but don't want to search it - boom.. enumerated columns prevent that mess up.

Side note - I still think having 40 similar columns is a silly idea and can be normalized out into one table with one column and 0 to n rows that are keyed back to a master table.. but then again with column names like "PR1" and "PR20" it's damn hard to know what this is holding.
Was This Post Helpful? 0
  • +
  • -

#7 skyshare  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-November 12

Re: Search whole table

Posted 30 November 2012 - 10:46 AM

Thanks for the reply
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1