SQL Search with parameter that could be date or could be string

Question

Hi there.
I have a search box where you can enter plain text. The keywords are separated by spaces. I check each keyword if it fits the format for a date and store 'all keywords' in one array and 'date keywords' in another. All the 'date keywords' are also in the 'all keywords' array, as I can not tell if its 'only' a date or maybe also a description or name. I search over many fields like name, description, etc...

I use something like

SELECT * FROM Tbl
WHERE NOT EXISTS(
    SELECT * FROM @SearchItems WHERE NOT( 
    name LIKE SItem OR
    description LIKE SItem OR
    field3 LIKE SItem)
)

This searches for entries, where no searchitems exist, that are not found -> Means, it shows only items, where all searchitems are found in any field. I use these two 'NOT's because otherwise I would have to check if the count of found items = count of all items.

But now I have a problem with the dates. Because 10/05/05 is a date but could also be a number in the description. So I want to get all items where there is a 10/05/05 in one of the normal fields, or the date matches the specific date.

But if i add somehting like

AND NOT EXISTS(
    SELECT * FROM @DateItems WHERE NOT(
    date = DItem)
)

It means, that the item needs to have 10/05/05 as string in a field AND as date, but if I use OR, it means that all item with the right day will be shown, regardless of the other keywords. If I delete the 'date-keywords' from the 'all-keywords' it would not find the string in the description.

How to fix this? I want to get all items from the date, or with the date in description when I enter '10/05/05' but if I enter 'blue 10/05/05' blue has to be in one of the fields and 10/05/05 could be in a normal field OR the date.

Edit: I forgot to mention: In the 'all-keywords' array, the dates are in original format like '10/05/05'. The keywords realized as dates are stored in the 'date-keywords' array in ISO format '2005-05-10'. So i cant do something like WHERE DItem = date OR description like DItem.

As this is a bit hard to describe, please ask if anything is unclear. Thanks for any help.

Answer 1

I found a solution. And because it took me a good while to get to this soltion i will write it down here, so someone will maybe get help from it.

Since a possible date also should be a regular search string, the search fails when this string is not found. The only exception is when the interpreted date of the string matches the date of the item (in which case the string representation does not need to be found somewhere). But to do this you need to know which 'date-keyword' belongs to which 'all-keywords' search string.

If you have this relation, it is possible to accept an item if the string representation is found OR if the date matches.

So now i load the dates with their string representations into a table and have a simple OR relation between them: WHERE Date = DateItem OR Field = StrItem.