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

roshan gravatar image

roshan
137 7 8 10

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

<!-- 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 -->
more ▼

answered Jan 19, 2011 at 12:26 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

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][1] included a complete sample how to do it, which you might find useful if you opt to go this route.

[1]: http://ask.sqlservercentral.com/questions/7307/how-to-update-the-session-datatable-from-cnet-to-sqlserver-2008?page=1
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][1] (if you already have main SSC account), it has Jeff's function, my own which uses xml and CLR function by Pavel Pawlowski.

[1]: http://www.sqlservercentral.com/Forums/Topic943562-338-2.aspx
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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

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

x1853
x991
x742

asked: Jan 19, 2011 at 11:31 AM

Seen: 2638 times

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