Where like query

[micang]

SQL 2000

Hi All,

I have a varchar column with descriptive data stored.

I need to pullout any record with 'aston' in it or 'martin'

Would a query like below do the trick?

select *
from [tablename]
where [description] like 'aston' or [description] like 'martin'

Basically I need to pull out anything where aston or/and martin are present in the field. e.g. aston martin, astonmartin,aston,martin,astonmartin123 etc etc

Many thanks

Michael

 

[erics55]

select *
from [tablename]
where [description] like '%aston%' or [description] like '%martin%'

think this is what you want

 

[micang]

thanks eric, will give that a try.

Michael

 

[micang]

Thanks eric, that did the trick.

Michael

 

[SQLBill]

Just keep in mind that using the wildcard at the beginning of the string will stop indexes from being used.

-SQLBill

Posting advice: FAQ481-4875

 

[micang]

thanks SQLBill, did not kow that.

Using the above as an example, is there a way around that then?

Many thanks

Michael

 

[SQLBill]

This is always a tough one. The searched for text can appear:

1. at the beginning of the text.
Aston is my best friend.
2. at the end of the text.
My best friend is Aston.
3. in the middle of the text.
a. with a space before it
My best friend, Aston, lives next door.
b. with with a space after it
My favorite car is the Aston Martin.
c. connected to another word.
My favorite car is often mispelled as AstonMartin.

To find #1, you can use LIKE 'Aston%'. But for the rest, you really do need the '%Aston%'.

-SQLBill

Posting advice: FAQ481-4875

 

[micang]

Thanks SQLBill, very good to know.

Can wildcards only be used with "like"? I mean if you use:

where [description] ='%aston%'

will that work too?

Ta

Michael

 

[SQLBill]

Yes and no. You can use the % with =...however, it won't work the same.

LIKE 'aston%'
will find aston, astonmartin, aston1, etc.

= 'aston%'
will only find aston%

-SQLBill

Posting advice: FAQ481-4875

 

[micang]

Thanks so much, so basically '%' using = is not a wildcard then?

Michael

 

[SQLBill]

Correct. = (equals) means 'an exact match'.

-SQLBill

Posting advice: FAQ481-4875