Searching Multi key word in SQL Server 2005

There are many types of searches operators applies in SQL Server SQL to find related data. One of the most famous opeator is Like operator. When we need to search any character from a string we use like opeator and append %% with parameter like

Select * from tbl where columnname like '%xyz%';

Now if user wants to enable google type search and ask for searching Multicharacters at a time. Then we have another solution for that type of search example as follows

Now we can see we have I and a in parameter string and using cross apply query can easly search both values.

How it works
1. fn_Split is a self made function that return a table with one column and it has all values seperated by comma. In above example we have 2 rows with "I" and "a" values respectivly. 
2. Cross apply duplicates all data against each value of fn_split. In this example 10 rows generated five with joins for "I" and five with "a" .
3. Using "item" column in where clause instead of parameter name search for both characters from 10 rows.
4. Distinct clause removes duplicate records from result set.

Now if we need to impliment this in muliple tables and columns as mostly required we can easily get our required result. as shown in example

NOTE: Remember this is a space hungry query as much values you have in your parameter, data will be duplicated but this will help you to enable google like search in your application.