criteria in select statetment - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

criteria in select statetment

Author

Topic

hybridGik
Starting Member

26 Posts

Posted - 2006-07-06 : 15:14:38

hi,

how would I select all ID values with a prefix of '100' and '300'?

here's the table sample:

ID DESC
---------------
100111 DESC1
200111 DESC2
300111 DESC3
201111 DESC4
305111 DESC5

sample output with a prefix of '100' and '300':

ID DESC
---------------
100111 DESC1
300111 DESC3

thanks.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-06 : 15:21:13

Select * from MyTbl where left(ID ,3) in ('100','300')

or

Select * from MyTbl where ID Like '100%' or ID Like '300%'

Srinika

hybridGik
Starting Member

26 Posts

Posted - 2006-07-06 : 15:27:18

i have that type solution but i need it to be dynamic. there could be more than 2 criteria. like '100', '200', '300'. my problem here is the LIKE clause. maybe there's a way to incorporate the '%' in 'IN' Clause. i don't know.

thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-06 : 15:36:59

-- Please explain ur Q in a bit detail
-- There r ways as dynamic SQL & Passing a comma seperated values as a parameter

Srinika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-06 : 15:42:52

[code]
declare @t table
(prefix varchar(3) not null primary key clustered)

insert into @t
select '100' union all
select '300' union all
... more criteria ...
select '837'

select
*
from
MyTbl
where
left(ID ,3) in
(select prefix from @t)

[/code]

CODO ERGO SUM

hybridGik
Starting Member

26 Posts

Posted - 2006-07-06 : 15:43:46

Select * from MyTbl where ID Like '100%' or ID Like '300%'

is what I need. But what if the criteria for ID is more than 2 ?

is there a way i could combine IN and LIKE clauses?

select * from tbl1 where ID in ('100%', '200%', '300%'...) <- like this.

thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-06 : 17:00:57

hybridGik,

From where r u getting these 100, 200, 300 , ... like stuff ?
Is it dynamic (means, in one query one set the other query a different set) ?
or is it too large for u to type in the whole set ?

-- MVJs answer above may be satisfying ur need

Srinika

hybridGik
Starting Member

26 Posts

Posted - 2006-07-06 : 18:08:32

the criteria is from the user. he/she may input 100,200,300 or 100 or 100,200,103,105

thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-06 : 22:52:46

i will go for mvj solution, don't complicate by using dsql

for the table to be used, search for fnsplitText (function that splits a comma delimited string into rows of data) in this forum or the blogs and use the resulting table as replacement for @t

--------------------
keeping it simple...

hybridGik
Starting Member

26 Posts

Posted - 2006-07-07 : 01:33:06

ha? sounds complicated to me

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-07 : 09:00:06

quote:
Originally posted by hybridGik

ha? sounds complicated to me

Sounds like whinging to me.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources