How to find if Given words exist in a table?

Here is your data repeated here:

declare @country table
(
    countryname varchar(50),
    code int
); 
insert into @country 
select 'India', 1 
union all select 'Indian Island', 1 
union all select 'srilanka', 1 
union all select 'islands of srilanka ', 1 
union all select 'china', 1 
union all select 'US', 2 
union all select 'canada', 2 
union all select 'UK', 1 
union all select 'France', 1;

-- to find the records where code = 1
-- and the country name contains
-- any of the words in the list:
select
    countryname, code
    from @country
    where
        code = 1 and
        (
            countryname like '%China%' or
            countryname like '%India%' or
            countryname like '%Srilanka%'
        );

-- the above select shows results like this:
--countryname                                        code
-------------------------------------------------- -------
India                                              1
Indian Island                                      1
srilanka                                           1
islands of srilanka                                1
china                                              1

-- To update the countries which have the code = 1,
-- but are not in the list to have code = 3:
update @country
set code = 3
    where
        code = 1 and countryname not like '%China%'
        and countryname  not like '%India%'
        and countryname not like '%Srilanka%';

-- the above will update 2 rows, namely with country name UK and France
-- it will update these records' respective code to 3 from 1

-- test your update
select * from @country

-- this will show results like this:
countryname                                        code
-------------------------------------------------- -----------
India                                              1
Indian Island                                      1
srilanka                                           1
islands of srilanka                                1
china                                              1
US                                                 2
canada                                             2
UK                                                 3
France                                             3

Can I get an A? :)

Oleg

<!-- Begin Edit

Per @roshan request, lets change the requirement to have the number of items in the list of words dynamic. This way, we don't have to have a complex and possibly long where clause:

After the original table is created and populated with data, declare table to keep one word per record:

declare @list table(word varchar(48));
insert into @list (word)
select '%China%'
union all select '%India%'
union all select '%Srilanka%'
union all select '%Something Else%'
union all select '%Insert as many as you need%';

-- to find the records where code = 1
-- and the country name contains
-- any of the words in the list:
select
    c.countryname, c.code
    from @country c inner join @list words
        on c.code = 1 and countryname like words.word;

-- To update the countries which have the code = 1,
-- but are not in the list to have code = 3:
update c
set code = 3
    from @country c left join @list words
        on countryname like words.word
    where c.code = 1 and words.word is null;

The above update will update 2 records like before.

End Edit -->

EDIT

Updated the SELECT-statement after Oleg pointed out I hadn't read the question good enough.

END EDIT

Have a look at Jeff Modens article about Tally tables: **http://www.sqlservercentral.com/articles/T-SQL/62867/**

Here's an example based on Jeffs article, which I think will solve your problem:

SELECT  TOP 10000 
       IDENTITY(INT,1,1) AS N   
INTO    dbo.Tally   
FROM    Master.dbo.SysColumns sc1,        
       Master.dbo.SysColumns sc2
GO
ALTER TABLE Tally ADD CONSTRAINT PK_Tally PRIMARY KEY (N)
GO
CREATE FUNCTION dbo.fn_Split(@StringToSplit nvarchar(max))
RETURNS @SplitTable TABLE(
    OrderNo int IDENTITY(1,1),
    SplitWord nvarchar(4000))
AS
BEGIN
    SET @StringToSplit = N',' + @StringToSplit + N','
    INSERT INTO @SplitTable (SplitWord) 
    SELECT SUBSTRING(@StringToSplit,N+1,CHARINDEX(N',',@StringToSplit,N+1)-N-1)   
    FROM dbo.Tally  
    WHERE N < LEN(@StringToSplit)    
    AND 
    SUBSTRING(@StringToSplit,N,1) = N','
    RETURN
END
GO
declare @country table(countryname varchar(50),code int)
insert into @country
select 'India',1 
union all 
select 'Indian Island',1 
union all 
select 'srilanka',1 
union all 
select 'islands of srilanka ',1 
union all  
select 'china',1 
union all
select 'US',2 
union all 
select 'canada',2 
union all 
select 'UK',1 
union all 
select 'France',1
--Updated SELECT-statement after Oleg pointed out a mistake of mine
select
    c.countryname, c.code
    from @country c inner join 
    (
        select '%' + SplitWord + '%' as word 
            from dbo.fn_Split('India,Srilanka,China')
    ) words
        on c.code = 1 and countryname like words.word;