question in using like and %

scorpioy

I tried to run some query using like and %:

select a.id, b.id
from AA a, BB b
where a.id like '%b.id%'

But it does not find anything even a.id has the same value as b.id

When I tried to put some variable between two %, it also doesn't work.

Do these imply that we can not use anything other than constant string between two % ?

How should I write query if I want retrieve the rows in which the value of a.id contains the value of b.id? (refering the query I wrote above)

Thanks.

akillez

The query will search field a for values containing 'b.id'.
(e.g. 'dumb.idiot', nothing personal, I just couldn't think of anything else containing b.id 😉 )

You can use the following pattern-expressions in like:

a constant
a special register
a host variable
a scalar function whose operands are any of the above
an expression concatenating any of the above

mourisj

I did something like

select a.txt, b.txt
from foo a , foo2 b
where a.txt like '%' || b.txt || '%'

and b.txt = 'abc'

in a test database, but this did not give me yet the results I expected. (In fact, it returns nXm lines, with n=rows in foo, m=rows in foo2). I did not yet understand this, but I did not yet think much about it.

Perhaps you should try:

$var = '%' || $previous_qry_result || '%';

select a.txt
from foo a
where a.txt like $var;

This one should work (it does directly in mysql if I do what I think it would do in a PHP script).

Have a nice day,

JJ Mouris

scorpioy

I changed to this:

select a.id, b.id
from AA a, BB b
where a.id like concat('%',b.id,'%')

and it seems working now.

But I got another problem in the stored procedure:

I wrote a procedure which has two IN parameters which are two rows' IDs in two tables. I tested the procedure on the console which works fine. But when I use PHP to pass in all rows' IDs in a loop, it no longer finds the row. I checked the loop and those two ID values do pass in, but it just does not work any more.

Here is some part of the procedure code:

CREATE PROCEDURE AD_book(IN AD_id varchar(30), IN book_id varchar(30))	

insert into today_found_book  

	select a.id, c.id, c.courseName, c.title, c.edition
	from AD_book a, book c
	where a.uniq_id = AD_id and c.uniq_id = book_id		and
		c.courseName like concat('%',courseN,'%')
	and
		c.title like  concat('%',tit,'%')	
	and
		c.otherInfo like concat('%',otherInf,'%')		and
		c.edition like concat('%',edi,'%')	;

I tested it on the console:
call AD_book('410a151500be6', '40f0753af2225');

and one row will be shown on the result table.

But when I use PHP to go throug two tables' rows and pass in all row IDs, it doesn't work anymore.

This thing really puzzled me. Could someone help me this? Thanks a lot.

Sxooter

SQL spec says || = string concatenation, MySQL says it's the OR operator...

So, you gotta use the concat function.

turning on the ansi switch makes MySQL behave in the SQL spec way.

scorpioy

Originally posted by Sxooter
turning on the ansi switch makes MySQL behave in the SQL spec way.

Could you tell me how to turn on the ansi switch? I'm fairly new to this. Please give me more detailed specification on this.

Thank you.

Sxooter

at startup, you pass a -ansi switch. On machines with package management, you can edit the file in /etc/rc.d/init.d

But it really depends on the OS you're running.

Note that turning on the -ansi switch will break most MySQL oriented database apps.

scorpioy

My machine is WinXP. I decided not doing the ansi switch because I found an alternative way of solving my problem. But I'm not quite understand what you wrote:

Note that turning on the -ansi switch will break most MySQL >oriented database apps.

What does it mean actually? What effects would it have?

Thanks.

Sxooter

http://dev.mysql.com/doc/mysql/en/ANSI_mode.html

It breaks many MySQL specific apps because most MySQL specific apps are written to use MySQL's non-ansi compliant idiosyncracies.