@roshan I have updated my answer so the list can be dynamic. Please let me know if this works. By the way, since you have SQL Server 2008, the best way to make it work is to create a stored procedure which accepts a given code and also a table valued paramater populated with the list of desired words. There was a question about 6 months ago asking about how to update the database table with values from C# session. My answer included a complete sample how to do it, which you might find useful if you opt to go this route.

@Oleg,Thanks for the answer. In the mean time i have written a Table valued function which converts CSV To CharTable. This function accepts the given words('India,srilanka,china') as input and displays them as a records in the table. Can this be used in anyway to reduce the code?? instead of writing 50 select union all.

@roshan Yes, but there is no need to write your own TVF to convert delimited data. You can use the one written by Jeff Moden, it is the best one. Please take a look at this discussion (if you already have main SSC account), it has Jeff's function, my own which uses xml and CLR function by Pavel Pawlowski.

@Magnus Ahlkvist This specific predicate will not work, what is needed is not the IN (some_list) but the LIKE predicate. I believe that my answer (after the begin edit part) does the trick. In other words, you should probably consider modifying your select to