Newbie question: searching a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Newbie question: searching a table

Author

Topic

Lienne
Starting Member

11 Posts

Posted - 2006-02-15 : 14:41:09

I'm somewhat new to sql (been doing only basic I/O queries) but my front end app now requires something a little more complicated. I want to be able to select all rows from a table that contain any instance of a search term. IE: user searches in a phone number field with the search term of "1" and should get all rows that have ANY instance of a 1 in that field, including in the middle of the number.

I'm pretty sure there are other posts about this sort of thing, but unfortunately I'm not even sure what it is that I'm looking for!

Thanks so much in advance.

> SELECT * FROM users WHERE clue > 0
0 rows returned

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-15 : 14:50:33

Look at Books Online - these topics: (there are examples)

LIKE
Charindex

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-15 : 14:52:45

You can use LIKE.

SELECT ColumnListGoesHere
FROM YourTable
WHERE YourColumn LIKE '%1%'

I'd suggest only letting them search on begins with though as that first percent will cause performance problems. So begins with would be search for anything that begins with 1 and that query would be:

SELECT ColumnListGoesHere
FROM YourTable
WHERE YourColumn LIKE '1%'

You should also look into full-text indexing.

Tara Kizer
aka tduggan

Lienne
Starting Member

11 Posts

Posted - 2006-02-16 : 15:34:52

Thanks guys! Worked like a charm. I needed the "LIKE" command as well as the wild card "%"

On to the second problem...how might I search multiple fields for the same search term (extending the above question). Right now I have a select query like tkizer's example in a two (nested) PHP loops. But I'm assuming there's probably a single-query way. Any suggestions?

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 15:36:27

SELECT ColumnListGoesHere
FROM YourTable
WHERE YourColumn LIKE '1%' OR SomeOtherColumn LIKE '1%' OR ...

Tara Kizer
aka tduggan

Lienne
Starting Member

11 Posts

Posted - 2006-02-16 : 16:37:31

Once again, thanks a bunch!

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources