SQL Like statment (reverse) - SQL Server Forums

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)

SQL Like statment (reverse)

Author

Topic

loneregister
Starting Member

6 Posts

Posted - 2004-08-09 : 13:45:08

How can I do a like statement that is reverse of normal.

Rather than looking for a field that contains a part of some static text, I want to find all fields that is part of some static text.

So -

select * from table where '%drill%' like %[field1]%

?

loneregister
Starting Member

6 Posts

Posted - 2004-08-09 : 13:46:28

sorry - rather

select * from table where 'drill' like %[field1]%

Thanks.

quote:
Originally posted by loneregister

How can I do a like statement that is reverse of normal.

Rather than looking for a field that contains a part of some static text, I want to find all fields that is part of some static text.

So -

select * from table where '%drill%' like %[field1]%

?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 13:47:05

I don't understand. Could you provide an example by posting some data?

Tara

loneregister
Starting Member

6 Posts

Posted - 2004-08-09 : 13:52:23

For example
If I have a table that has the following:

table1
[field1]
drill
drills
dri
power drill
cordless drills

if I have a static value of powerdrill

when I do the search

select * from [table1] where [field1] like '%powerdrill%'
nothing get's returned

However, if I reverse that (and here's my syntax question)

select * from [table1] where 'powerdrill' like %[field1]%
I would like to get
1 record with the value of drill returned.

However, the above syntax fails, and I don't know how to make it work or what the proper syntax is.

Can anyone help me in the right direction?

Thanks!

quote:
Originally posted by tduggan

I don't understand. Could you provide an example by posting some data?

Tara

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 13:55:48

I'd use something like:

SELECT *
   FROM myTable AS a
   WHERE  'Powerdrill' LIKE '%' + a.name + '%'

-PatP

loneregister
Starting Member

6 Posts

Posted - 2004-08-09 : 13:58:15

excellent! Thanks!

Kevin

quote:
Originally posted by Pat Phelan

I'd use something like:
SELECT *
FROM myTable AS a
WHERE 'Powerdrill' LIKE '%' + a.name + '%'
-PatP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 13:59:43

I think you are going to need to use SOUNDEX.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 14:04:00

How is "Powerdrill" going to find "power drill" without a soundex search?

Tara

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 15:43:40

It won't find it without Soundex, but then again it won't find it using Soundex either (the codes will never match). However, my suggestion would find both "power" and "drill" for the "Power drill" example.

I took the liberty of answering the original question, instead of exactly solving the demo that was posted later. The poster wanted to know how to find out which rows were a subset of some constant string using LIKE. This is a common task when doing etemological searches (like a KWIK index scan) and certain kinds of cryptography, although a relational database is rarely used!

Sorry if I confused things!

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources