Hi Jess,

>cDesc is variable which means the string don't have specific length and have no specific no. of words typed. It can have "Chol Vibrio cholera biovar" in any order.

You can easily get around the problem of no fixed positions with one line of code:

? "WHERE field1 LIKE '%" + STRTRAN(cDesc, " ", "%' OR field1 LIKE '%") + "%'"

Output:

WHERE field1 LIKE '%one%' OR field1 LIKE '%two%' OR field1 LIKE '%three%'

So you have one line of code to build your complex WHERE clause. You can have as many words in your variable as you want/need.

>Something similar to Where cdesc $ description without truncating the words which are separated by spaces. And the SQL Syntax must be ANSI to make it SQL Server compatible. I know that there is a workaround but I need to confirm if there is a simplified alternative.

IMO, there's no such solution.

Regards,
Armin