Like - Wildcard

Database

Like - Wildcard

Is there anything like Like all or Like any command...

if we include more than one field inside the sql.

for eg:

where

first_name like ('ad%', 'w%', 'e%')

Do we need to include ANY OR ALL....

first_name like all('ad%', 'w%', 'e%')

first_name like any('ad%', 'w%', 'e%')

if so what is difference between having an All and Any??
4 REPLIES
N/A

Re: Like - Wildcard

It is just like you say:

where
first_name like ANY ('ad%', 'w%', 'e%')

will find adrian, william and eleanor

first_name like ALL ('ad%', 'w%', 'e%')

will not find anything because you cannot build a string which starts with AD, W and E!

But first name like ALL ('%an%','%w%, '%e%')

will find Andrew but not adrian, william or Eleanor.

Re: Like - Wildcard


To add more to Jimm reply...

The ALL and ANY is quantifiers for 'LIKE' operator, these quantifiers are used to indicate how many string patterns must be found in the string expression.

ANY - one or more pattern should match in the string expression
ALL - All patterns in the list should be match.

Regards,
Balamurugan

Re: Like - Wildcard

thank you both!!

related to this i also got another doubt..

the query which im executing is kinda big..so i need to create a global temp table or derived table...

how do i do it...

do i need to include the database name before the table name??

for example...for the following query..how to i create a temp table or derived table....

sel e.firstname, c.year, e.month, sum(c.salary)

from employers e

join customers c

where c.month_key = e.month_key

on month like any (%m%, %S%, %j%)

group by 1,2,3

Also, do i need to run the gtt every time before running the main query in which gtt is included??

Re: Like - Wildcard

Just create the global temporary table with the same definition as your select columns and index

ex:
CREATE global temporary table table1( A INTEGER NOT NULL,
B INTEGER NOT NULL)
on commit preserve rows;

insert into table1(
your select query)

sel * from table1;

Thanks
Rajeev T