I need to write a SQL Server function that will take in two fields and perform a logical operation in this manner
- take first field value, split it into words
- make sure all of these words exist in the second field.
For example: the first field of "le chien" and the second is "le chien joue avec la balle" The function would return true in this case, since all the words from the first one are in the second.
I could simply use SUBSTRING:
SUBSTRING(field1, 0, 5)=SUBSTRING(field2, 0,5)
But in some cases it would not work; if I have field1 as la maison (1) and field2 as la maison (2) SUBSTRING(field1, 0, 5)=SUBSTRING(field2, 0,5) would return true even though the strings do not match.
CHARINDEX(field1, field2) > 0 works but not for all the cases, since it only works for those strings where words are in the same order.
for example it works for "le chien" and "le chien joue avec la balle" but does not for "le chien" and "chien le joue avec la balle"
Increasing number of characters in some cases would work, but would not in others. So it all boils down to writing a function that will split the first string into separate words and ensure that all of them exist in the second string.
Can someone help me build a function or point me in the right direction? I'd really appreciate any help
P.S> I need something that I can just plug in to the select statement as a condition in a where clause