x

How to find if Given words exist in a table?

Hi, I am trying to find a solution for the below problem, but could not think of any way. The problem is, i need to find if a givenword exist in a table.

 declare @country table(countryname varchar(50),code int)
 insert into @country
 select 'India',1 
 union all 
 select 'Indian Island' 
 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

if 1-- then Asian country if 2- north american country if 3-- rest of the world

given list of words 'India,Srilanka,China'.

The question is: get country names with value "1" and is in the given list of words. If the countryshows up with value 1 but not in the list of words, then code need to be changed to 3.

Expected output : india 1 indian islands 1 srilanka 1 islands of srilanka 1 china 1

if select * from @country then expected result is india 1, indian islands 1, srilanka 1, islands of srilanka 1, china 1, us 2, canada 2, uk 3, france 3. plzz help me out how to accomplish this.

thanks in adv

more ▼

asked Jan 19, 2011 at 11:31 AM in Default

avatar image

roshan
137 7 8 13

Sorry but it smells homework!?

Jan 19, 2011 at 12:34 PM Håkan Winther

i have just give a test data. i would have written the same ans as Oleg submitted if it was a homework. But i want something more.

Jan 19, 2011 at 01:03 PM roshan
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Jan 19, 2011 at 12:26 PM

avatar image

Oleg
17.1k 3 7 28

I was expecting something which could be applied to any no(in given words). what if i have 50 given words then i cant put %@word% 50times in my where clause.

Jan 19, 2011 at 12:36 PM roshan

@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.

Jan 19, 2011 at 01:08 PM Oleg

@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.

Jan 19, 2011 at 01:44 PM roshan

@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.

Jan 19, 2011 at 02:15 PM Oleg
(comments are locked)
10|1200 characters needed characters left

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;


more ▼

answered Jan 19, 2011 at 03:57 PM

avatar image

Magnus Ahlkvist
21.1k 19 39 42

@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

select
    c.countryname, c.code
    from @country c inner join 
    (
        select SplitWord 
            from dbo.fn_Split('%India%,%Srilanka%,%China%')
    ) words
        on c.code = 1 
        and c.countryname like words.SplitWord;

or restate the above as cross apply :)

Otherwise, you will not get back the result for, say, 'islands of srilanka' record.

Same goes for the update @roshan requested:

update c
set code = 3
    from @country c left join
    (
        select SplitWord
            from dbo.fn_Split('%India%,%Srilanka%,%China%')
    ) words
        on c.countryname like words.SplitWord
    where c.code = 1 and words.SplitWord is null;
Jan 19, 2011 at 04:07 PM Oleg

Oh, you're right. I over-simplified thinkgs.

I'm guessing that the input string comes from outside and cannot easily be changed to '%India%,%Srilanka%,%China%'

Instead I would do

 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;
Jan 19, 2011 at 04:15 PM Magnus Ahlkvist

Yes, this is a very good catch, I definitely missed this one. select '%' + SplitWord + '%' as word is much better than what I had in my comment, sandwiching each word between the percent signs :)

Jan 19, 2011 at 04:26 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2079
x1067
x994

asked: Jan 19, 2011 at 11:31 AM

Seen: 3227 times

Last Updated: Jan 19, 2011 at 12:52 PM

Copyright 2016 Redgate Software. Privacy Policy