Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 644
  • Last Modified:

Combine "like" and "in" ??



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
_TAD_
Asked:
_TAD_
  • 5
  • 5
  • 2
  • +4
1 Solution
 
Scott PletcherSenior DBACommented:
Nope, don't think you can do that.  AFAIK, you will have to code the LIKEs and the IN separately.
0
 
_TAD_Author Commented:


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
 
Mikal613Commented:
select * from table where col1 in (Select * from table where col1 like 'A%', like 'B%', 'Max')
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
_TAD_Author Commented:

Mikal...

  I didn't think that was possible  >>>  col like 'A%', like '%B'      ...etc
0
 
Mikal613Commented:
select * from table where col1 in (Select col1 from table where col1 like 'A%', like 'B%', 'Max')

0
 
Mikal613Commented:
what do you mean?
0
 
Mikal613Commented:
FINAL QUERY

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

0
 
_TAD_Author Commented:

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
 
Mikal613Commented:
did you try it?
0
 
jonleehackerCommented:
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
 
appariCommented:
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
 
PaulBarbinCommented:
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
 
_TAD_Author Commented:



PaulBarbin...

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



Does SQL server 2000 support regular expressions inside the query strings?
0
 
illCommented:

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
 
PaulBarbinCommented:
Yes, Like allows for regular expressions.  Look in BOL under LIKE, and then Pattern Matching in Searches.

Paul
0
 
_TAD_Author Commented:


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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 5
  • 5
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now