Combining "like" and "in" in SQL statement

can this be done?

[graywolf]

I am trying to combine the following sql statemnts into one statement

select <col1> from <table1> where <col2> like '123%'
select <col1> from <table1> where <col2> like '456%'

select <col1> from <table1> where <col2> in ('123456', 45678')

I tried a couple different variations and nothing worked like

select <col1> from <table1> where <col2> in ('123%', '456%')

I know I could build them into an array and issue each stament seperately. I was trying to do it all in one to keep the programming neater.

[txbakers]

How about a UNION statement?

select <col1> from <table1> where <col2> like '123%'
UNION ALL
select <col1> from <table1> where <col2> like '456%'

This would keep it in one statement.

However, I know that mySql (current version) doesn't support the UNION command yet.

[Dreamquick]

Altenately put the like statements into a temporary table and then join this back to the original table using a LIKE operator in the join criteria...

CREATE TABLE <temptable>(
MyPattern DATATYPE
)

INSERT INTO <temptable>( MyPattern ) VALUES( '123%' )
INSERT INTO <temptable>( MyPattern ) VALUES( '456%' )

SELECT A.*
FROM <table1> AS A
LEFT JOIN <temptable> AS B ON A.<col1> LIKE B.MyPattern
WHERE B.MyPattern IS NOT NULL;

It's overkill but it does the job...

- Tony

[graywolf]

The union worked perfectly! Didn't think of that thanks!

[amoore]

Seems like this may be more efficient:

select <col1> from <table1> where <col2> like '123%' or <col2> like '456%';

[txbakers]

D'OH! Of course the "or" is much more efficient than the union.