Select only record containing a string frm a list? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select only record containing a string frm a list?

Author

Topic

paolosavona
Starting Member

6 Posts

Posted - 2010-04-09 : 05:09:42

Select only record containing a string frm a list?

I have a table, and I need to select only records
where a certain field contains one of the values included
in a given list (say another single-field table).

Is there a direct command, or I need a loop
checking one by one all the values list?

Thanks, bye.

Paolo

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-09 : 05:23:23

Hi,

Just try it and let me know whether this was your requirement or something else.

Declare @Lookup varchar(100)

set @Lookup ='raver'

Declare @Test table
(Srno int ,
Sname varchar(50))

Insert into @Test
select 1,'Jack' union
select 2,'Jock super' union
select 3,'Super raver mast' union
select 4,'Saver' union
select 5,'Jock raver'

select * from @test
where Sname like Cast('%' + @Lookup + '%' as Varchar(100))

Regards,
Bohra

paolosavona
Starting Member

6 Posts

Posted - 2010-04-09 : 05:46:38

You are very kind, Bohra, definitely.
-------------------------------------

I try to be more explicit.
Say this in my TableBase:

Red bloom
Blue ribbon
Green Leaf
Yellow umbrella
Red table
Violet dream
Orange blossom
Green tea
Indigo virgin
Black sabbath

And this is my SearchedList

Red
Green

The code I need should find all and only the following:

Red bloom
Green Leaf
Red table
Green tea

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 05:50:03

the SearchedList is a table / table variable ?

KH
[spoiler]Time is always against us[/spoiler]

paolosavona
Starting Member

6 Posts

Posted - 2010-04-09 : 05:51:58

Yes, it's another table of my db.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 05:52:46

You can put the values you want to search for into a table variable (or temp table) like this:

Declare @Lookup  table
(Search varchar(50))

INSERT INTO @Lookup 
SELECT 'Jack'
UNION ALL SELECT 'Jock'

Declare @Test table
(Srno int ,
Sname varchar(50))

Insert into @Test
select 1,'Jack' union
select 2,'Jock super' union
select 3,'Super raver mast' union
select 4,'Saver' union
select 5,'Jock raver'

SELECT t.*
FROM @Test t 
INNER JOIN @Lookup l
	ON t.Sname like '%' + l.Search +'%'

There are 10 types of people in the world, those that understand binary, and those that don't.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-09 : 06:01:58

Declare @Lookup varchar(100)

Declare @TempReq table
(ItemName varchar(50))

Insert into @TempReq
select 'RED' union
select 'Green'

set @Lookup ='''RED'',''GREEN'''

Declare @Test table
(Srno int ,
Sname varchar(50))

Insert into @Test
select 1,'Red bloom' union
select 2,'Blue ribbon' union
select 3,'Green Leaf' union
select 4,'Yellow umbrella' union
select 5,'Red table' union
select 6,'Orange blossom' union
select 7,'Green tea' union
select 8,'Indigo virgin' union
select 9,'Black sabbath'

SELECT t.*
FROM @Test t
INNER JOIN @TempReq l
ON t.Sname like '%' + l.ItemName +'%'

I just formatted the reply of "DBA in the making" for your example.

Regards,
Bohra

paolosavona
Starting Member

6 Posts

Posted - 2010-04-09 : 06:05:09

So, you mean that somehow I must re-write the whole Tablebase within the code body?
This becomes very bulky if the TableBase if very large...

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 06:07:30

quote:
Originally posted by paolosavona

So, you mean that somehow I must re-write the whole Tablebase within the code body?
This becomes very bulky if the TableBase if very large...

No, you can use temporary tables.

What format is your list of search matches in?

There are 10 types of people in the world, those that understand binary, and those that don't.

paolosavona
Starting Member

6 Posts

Posted - 2010-04-09 : 06:16:27

It is a single-field (text) table,
one of the tables of my db.

TableBase and SearchedList

are both tables already present in my db.
Say they are both single-field, (text), np for now.

What I need is a query taking from Tablebase all and only the records
of Tablebase where one (or more) of the elements of SearchedList is included in the text value.

Say, "red" is included in "red table",
but if I had a record "red green table" in TableBase,
this shd be selected too (and only once).

I hope I am clear enough, a bit rusty in coding, lol.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-09 : 06:22:01

As you said that both tables are single field, you can use Distinct keyword and u will not get duplicate record.

Regards,
Bohra.
I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources