Quering a word from a column, based on a longer word as input

[duftstenen]

Hi

I am in a situation where I need to see if a string matches a value in a SQL database. The input word will always be longer than the word in the database. I must admit that after thinking of a solution, I cannot come up with something usefull.

I have the following table, Category, one column called "FruitName". It contains the following 2 rows:

Apple
Orange


The problem is that I would like to query whether some of the value in the string e.g. "Applecake" exists in the Category table.

The only way I can figure out to solve this, is to populate every rows in a e.g. a list / array, where I run thru every item and try a regex match with the word in the database on the input string.

Do you guys have any good input on doing this the query way or some how else? I don't think the solution of running thru every row to match the specific string would be most optimal.

Best regards
D.

 

[jdemmi]

Can you do something similar to this? I don't know if you're using a program to pass in the searched value (applecake) or what...

select * from YOURTABLE
where INSTR('APPLECAKE',FruitName) > 0

-- Jason
"It's Just Ones and Zeros

 

[markros]

I think you meant

select * from YourTable where CHARINDEX(FruitName, @myString) > 0

But I don't really like the solution - it would not be optimmizable.

However, at the moment I can not think of a better one anyway.

 

[jdemmi]

Yes, I had my Oracle hat on...

-- Jason
"It's Just Ones and Zeros

 

[duftstenen]

Thank you both very much for the fast answer. It was exactly what I was look for, great! Let me if you work something out on a more optimal way of doing it.