is there another way of using ..? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

is there another way of using ..?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-12-23 : 18:23:44

kingston writes "hi
i have a query like this

SELECT * FROM table WHERE col LIKE '%text%' or col LIKE '%text'

in the above query i have to specify the "col" everytime to compare with the search condition. is there any alternate way where i can use the "col" once and "searchtext" many? kindly let me know about the same."

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-12-23 : 23:36:53

quote:

kingston writes "hi
i have a query like this

SELECT * FROM table WHERE col LIKE '%text%' or col LIKE '%text'

in the above query i have to specify the "col" everytime to compare with the search condition. is there any alternate way where i can use the "col" once and "searchtext" many? kindly let me know about the same."

I don't think there is any need for the second OR
select * from table where col LIKE '%tesxt%'

will give u the desired results .

Expect the UnExpected

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-02 : 04:09:06

What Harshal says is true, but, in the more general sense, the answer to your original question is no, you have to repeat the column name for each comparison that you want to make.

-------
Moo.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-02 : 05:01:50

If you want to use full text indexing then you could use the following

SELECT *
FROM Table
WHERE CONTAINS(col, '"*text*" OR "text*"')

However it's like harshal_in says if indeed all you looking for is %text% or %text and text is the same then %text% is all you need.

Besides full text indexing might be overkill.

Edited by - ValterBorges on 01/02/2003 05:10:52

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-02 : 06:11:46

select distinct *
from tbl ,
(select s = '%text1%' union select '%text2%') as searchstr
where tbl.col like searchstr.s

or put the strings in a table variable or temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-02 : 07:49:39

Nicely Done NR.

Just curious,
Have you measured the performance of your method vs. LIKE OR LIKE.?

I guess it would also depend on how many LIKE's you have or'd.

Edited by - ValterBorges on 01/02/2003 07:49:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources