Solved

Combine "like" and "in" ??

Posted on 2003-12-11
16
639 Views
Last Modified: 2008-03-06


Is there a way to combine the "like" and "in" commands?



Here is my current query:


Select * from table where col1 like 'A%' or col1 like 'B%'


I would prefer to have a query like:

select * from table where col1 in (like 'A%', like 'B%', 'Max')

0
Comment
Question by:_TAD_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +4
16 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 9922864
Nope, don't think you can do that.  AFAIK, you will have to code the LIKEs and the IN separately.
0
 
LVL 22

Author Comment

by:_TAD_
ID: 9922884


I figured as much, but it couldn't hurt to ask.


I'll leave this question open for a few more hours to see if anyone has found any wild or interesting way around this constraint.

Thanks!
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9923091
select * from table where col1 in (Select * from table where col1 like 'A%', like 'B%', 'Max')
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 22

Author Comment

by:_TAD_
ID: 9923203

Mikal...

  I didn't think that was possible  >>>  col like 'A%', like '%B'      ...etc
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9923246
select * from table where col1 in (Select col1 from table where col1 like 'A%', like 'B%', 'Max')

0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9923249
what do you mean?
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9923251
FINAL QUERY

select * from table where col1 in (Select col1 from table where col1 like 'A%' or like 'B%' or col = 'Max')

0
 
LVL 22

Author Comment

by:_TAD_
ID: 9923403

Mikal

I'm still pretty sure that you can't do that.

This statement needs the word COL1 at least once more.

select * from table where col1 in (Select col1 from table where col1 like 'A%' or like 'B%' or col = 'Max')


besides, isn't that SQL statement a bit redundant?
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9923450
did you try it?
0
 
LVL 1

Expert Comment

by:jonleehacker
ID: 9924561
I think this is what you're after:

select * from table where col1 in (Select col1 from table where col1 like 'A%'
                                                          or col1 like 'B%'
                                                          or col1 = 'Max')


0
 
LVL 39

Expert Comment

by:appari
ID: 9925587
try something like this

select * from table where substring(col1,1,1) in ('A', 'B') or Col1 ='MAX'

this works only if you want to search like 'A%','B%'..., if you want to search '%A%','%B%', i think no other way than to use multiple like conditions.

0
 
LVL 9

Accepted Solution

by:
PaulBarbin earned 50 total points
ID: 9925788
One last try:

When using pattern matching, you can search for sets or ranges that might help you out.  The set option is similar to the IN keyword but better because you don't need to separate by commas.

For example:

CREATE TABLE PatternTest (FieldtoSearch varchar(20))

INSERT INTO PatternTest Values ('Ant')
INSERT INTO PatternTest Values ('Bat')
INSERT INTO PatternTest Values ('Cat')
INSERT INTO PatternTest Values ('Dog')
INSERT INTO PatternTest Values ('Elephant')
INSERT INTO PatternTest Values ('Fox')
INSERT INTO PatternTest Values ('Gorilla')

To find all rows that start with the letters A, C, E, or G, you would use this statement:
SELECT * FROM PatternTest WHERE FieldtoSearch LIKE '[ACEG]%'

To find all rows that start with A, B, C, D, or E, you would use this statement
SELECT * FROM PatternTest WHERE FieldtoSearch LIKE '[A-E]%'

How's that?
Paul

0
 
LVL 22

Author Comment

by:_TAD_
ID: 9926022



PaulBarbin...

That almost looks like you are using something like Regular Expressions.



Does SQL server 2000 support regular expressions inside the query strings?
0
 
LVL 12

Expert Comment

by:ill
ID: 9926669

CREATE function  dbo.strSplitx ( @string varchar( 8000), @splitter char( 1) )
returns @res table ( id bigint primary key, rank bigint, val varchar( 8000) )
as
begin
      if substring ( @string, len ( @string), 1)<>@splitter
            set @string= @string+@splitter
      declare @start bigint, @word varchar( 8000), @charindex bigint, @i bigint
      set @i=1
      set @start=1
      set @charindex= charindex( @splitter, @string, @start)
      while (@charindex <> 0)begin
            set @word= substring( @string, @start, @charindex - @start)
            set @start= @charindex +1
            set @charindex= charindex( @splitter, @string, @start)
            insert into @res  values ( @start, @i, @word)
            set @i=@i+1
      end
      return
end
------
select * from dbo.strSplitx( 'A%,B%,MAX' , ',')   join  table on  ( col1 like val))
0
 
LVL 9

Expert Comment

by:PaulBarbin
ID: 9928620
Yes, Like allows for regular expressions.  Look in BOL under LIKE, and then Pattern Matching in Searches.

Paul
0
 
LVL 22

Author Comment

by:_TAD_
ID: 9931110


Excellent!

The regular expressions isn't *exactly* what I wanted, but it does get me 90% of the way there.


I think if I play with the regular expressions a little more I may be able to hammer out my flaws.  

I have used regular expressions before, but I am not an expert.  With a little practice I think I'll get where I need to go.


Thanks everyone!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 65
Use SSRS to email customers? 4 29
How do i delete the last node in an xml in T-SQL 7 28
sql server string_split 4 28
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question