Filtering on a part of a field - 3

Hello,

new question about an old topic (http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1232451&SiteID=1):

I want to set a filter on a field, but the it must set a filter on a part of the field. I think it will be more clear with an example:

I have a list with ItemNumbers and ItemDescriptions.
ItemNo ItemDescription
1 Pineapple yellow
2 Cauliflower size 6
3 Orange sweet yellow
etc. etc.

Now I want that the user of this report can set a filter just by entering a part of the description. So, if he enters 'flower' he only see the cauliflower. If he enters 'yellow' he only see the pineapple and the orange, etc. etc. If a blank value is left, all the items must be shown.

After this, Jarret, suggest to me to insert the next code into my query:

where ItemDescription like '%' + @ItemDescParam + '%'

This helped, but I want to have 2 options extra:
1) The part of the description which the user inserts must be independent of capitals
2) It also have to be possible that the user inserts more than one part. For example "pine" and "fellow"

Can some one help me out

Thx again

As for question #1...

Whether or not the case of the string is restricting the results is determined by the collation on your ItemDescription field. In Management Studio, run 'sp_help' on the table with the ItemDescription column in it. Look at the collation on the ItemDescription column, it will need to be something that is case-insensitive. Can you post the collation on your field

In my database, I have a field on one of my tables with a case-sensitive collation (SQL_Latin1_General_CP1_CS_AS). The CS distinguishes that it is case-sensitive. So, in order for me to do a case-insensitive search, I have to explicitly change the collation on the column. For your query, it would look something like this:

where ItemDescription COLLATE SQL_Latin1_General_CP1_CI_AS like '%' + @ItemDescParam + '%'

For question #2...

What would the user type in the search box, "pine yellow" Also, what if the user typed in "yellow pine", should it also return the Pineapple yellow record I think you could do this if the user was required to type the search values in the order they show on the record, by doing this:

where ItemDescription COLLATE SQL_Latin1_General_CP1_CI_AS like '%' + replace(@ItemDescParam, ' ', '%') + '%'

In this situation, if the user typed in "pine yellow", it would return the Pineapple yellow record. However, if the user typed in "yellow pine", it wouldn't find any results.

Hope this helps.

Jarret

Jarret,

thx again!

Question 1 is solved! :). In the begin it was hard for me to find the collation, but I found it on the field in the table, right click on the mouse, properties.

Question 2: Exactly as you said it work out when typed in "pine yellow" but not after "yellow pine". Is there a way to solve it so "yellow pine" is also vallid

Thx for helping!

As for the "yellow pine" situation, I think you'll need to take steveareno's advice and go with Full Text Indexing to be able to handle it.

Jarret