Is there a conditional expression for SQL?

instead of 'where field = string'

[too much information]

What I want to do is pull all records where a certain field contains a string of characters. Take a URL for example. so my field may contain:

www.domain.com
www.other.com/page.htm
www.domain.com/links.html
www.something.com/pages/page.htm
www.domain.com/about.html
etc.

if I want to pull all of the records that contain 'domain.com' can I just say:

select * from table where field contains 'domain.com'

or do I need to pull everything and loop through the data to check?

[TheNige]

select * from table where field like '%domain.com%'

If you have lots of records and lots of text you may want to look into SQL Full Text Search

[mattglet]

FYI - anything with a LIKE clause in the statement will take a lot longer to search through. so if you have a lot of records (say a referrer log) to search through, you could be waiting longer than usual to get your results.

-Matt

[aspdaddy]

it would probably faster to let the db server do this with LIKE, than to pull all the records and run a search in code.

You could experiment by creating a temp index and as you are searching from the start of the string only, it will be optimised

like '*www.domain.com'

[WebJoe]

AFAIK index can't be used at all if you have a wildcard at the beginning of your expression, as in aspdaddys

like '*www.domain.com'

plus only M$ Access supports * as a wildcard, and only in its built in wuery tool. If you connect with ado it'll work just any other SQL-DB, with the wildcard %

[aspdaddy]

hmmm.. maybe I misunderstood, but if you are searching for www.domain.com/<anything here> an Index will be very useful to order the column and limit the search

But a correction, the * (or its ASP equivalent) should be after the string

LIKE 'www.domain.com*' :)

Best bet is to experiment, the perfoemance may be fine .

[WebJoe]

I guess you did, so to clarify:

like '%yourquery'

: the index is not used

like 'yourquery%'

: the index is used

<added>just noticed, you mentioned the correction to your typo</added>