How to move not like string into table?? - 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

How to move not like string into table??

Author

Topic

yeung_lcd
Starting Member

5 Posts

Posted - 2012-12-04 : 22:14:42

Say I have a sql query like this:

Declare @table1 TABLE(id INT, field1 VARCHAR(100))
INSERT INTO @table1
SELECT 1,'Apple Juice'
UNION ALL SELECT 2,'Orange Juice'
UNION ALL SELECT 3,'Coffee and tea'
UNION ALL SELECT 4,'randam text...'
UNION ALL SELECT 5,'I eat an apple everyday'

Select * from @table1
	where field1 not like '%apple%' and field1 not like '%orange%'

Now I want to put the hard code values "apple" and "orange" into a table so that I can update it without modifying SP or Query string in asp file.

How can I do it?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-12-04 : 22:23:24

how about create a view for it ?

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-05 : 06:58:42

You can put the search terms into a table and join to that table - but you will always be querying for the terms that table. Is that what you really want?

Do you get the search terms from the asp.net side? If you are, are you sending those search terms to a stored proc? Give a bit more detail on what you are trying to do.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-05 : 08:20:56

create table SearchTerms
(
s varchar(20)
)
go
insert SearchTerms select 'apple'
insert SearchTerms select 'orange'
go
select t1.*
from @table1 t1
left join SearchTerms s
on t1.field1 like '%' + s.s + '%'
where s.s is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yeung_lcd
Starting Member

5 Posts

Posted - 2012-12-05 : 21:31:57

quote:
Originally posted by sunitabeck

In fact I am working on a classic asp file... the query string is in the file and one user want to add some filters to the result (not parameter).

The user may update filter terms later on so I am looking for some shortcuts to do it.

quote:
Originally posted by nigelrivett

thx, I will try it :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources