Trying to better understand the LIKE

Databases
1

Hello all,

If we do something like this:

SELECT d.nomeDominio FROM dominioFccn d WHERE d.nomeDominio LIKE %''%;

we call ALL the values listed.

I was expecting to have NONE returned. :s

Why is that?

Why am I asking this? Because I would like to do:

$stmt = $this->_dbh->prepare("SELECT d.nomeDominio FROM dominioFccn d WHERE d.nomeDominio LIKE ?");
$stmt->bindValue(1, '%' . $dominioVo->getNome() . '%', PDO::PARAM_STR);

But each time getNome() is empty, I get ALL the values. :s

Can I have your help to find out a solution for this?

K. Regards,
Márcio

2

Let’s say I want to search for “ABAC”

If I do LIKE %ABAY% will it find “ABAC” ? At this moment, he is finding. However, I’m not sure if this is a problem on my ss script, or a miss interpretation of LIKE.

Probably the first.

I cannot test it at the moment but I will once I get to the proper computer to do so. Unless, of course, I can get the answer from you. :smiley: (I will test it still, so I believe… oh well… )

:wink:
K. Regards,
:sleeping:
Márcio

3

Yup. I’ve done it almost like that.

Since I’m only interested on running the query if getSomething is NOT null, I end up putting everything inside that conditional.

I will do some more tests with LIKE to see what it likes and what it don’t likes. :smiley:

Thanks a lot for your reply,

K. Regards,
Márcio

4

Probably because % is a wildcard that means:
“Matches any number of characters, even zero characters”

So, if we add this on a empty string, we still get:
“Matches any number of characters, even zero characters”

  • an empty string (that plays no role here).

So, we get them all.

Márcio

5

%ABAY% will find ABAY and ZABAYDD but not ABAC since a C isn’t a Y

6

LIKE works really simply as there are only two wildcard characters.

% represents zero or more of any character
_ represents exactly one of any character

all other characters represent themselves and apart from the two wildcard characters LIKE basically functions the same as =

7

you have a front end programming language… use it! :slight_smile:

pseudo-code:

[COLOR="Red"]if getnome() is empty
then[/COLOR]
   SELECT ... FROM ...
[COLOR="red"]else[/COLOR]
   SELECT ... FROM ... WHERE something LIKE '%[COLOR="Blue"]something[/COLOR]%'
[COLOR="red"]endif[/COLOR]

simple, yes?