How to select rows with a particular substring?

S

Sharp

Hi

Consider the following field FOO containing data of type 'text'.

FOO
aaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbb
aaaaaaaacccaaaaaaa
aaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbb

I would like to extract rows containing the substring "ccc".
I expect the following result:

aaaaaaaacccaaaaaaa

What I have tried that did not work:
WHERE Foo LIKE '%ccc';

Any help appreciated.

Cheers
Michael
 
W

Wayne Morgan

You used the wild card %. Are you doing this on a SQL Server or using just
Access? For Access, the wild card is * unless you've told it to use ANSI 92
Syntax. You may find that

Like "*ccc*"

will work. I'm not sure if SQL Server will allow the double wild cards.
Another option would be a calculated field using InStr to find if the value
is in the string. Use >0 for the criteria.
 
S

Sharp

I am using Access 97.
This does explain why it doesn't work.

With the second option (calculated field), do you mean something like this?
Where InStr(Foo) = "ccc" >0;

Cheers
Michael
 
W

Wayne Morgan

Yes, but it would probably be

Where InStr([Foo]) = "ccc" >0;

even though there is no space in the field name.
 
V

Van T. Dinh

.... WHERE InStr(Foo, "ccc") > 0

See Access VB Help for other arguments which I omitted.
 
V

Van T. Dinh

It is in your Access (VB) Help. Access Help / Access VB Help should be your
_first_ source of information.
 
Top