SEO

--- Nederlands - Dutch ---

In MySQL is het percent-teken % de joker. Deze joker kan staan voor
één of voor meerdere tekens, daar wordt in feite geen onderscheid
in gemaakt. Bestaat er een manier om deze beperking te omzeilen? Ik
zou een zoekinterface willen aanbieden waarbij men de keuze heeft
tussen een wildcard die precies één teken voorstelt en een wildcard
die één of meerdere tekens kan voorstellen.

--- Dutch - Nederlands ---

--- English - Engels ---

In MySQL, the percent sign % is the wildcard. This wildcard
represents one or more characters, i.e. differentiation between one
or more characters isn't possible in a straightforward way. Is
there a way to avoid this limitation? I would like to build a
search interface that offers the possibility for the user to choose
between a wildcard that represents exactly one character and a
wildcard that represents one or more characters.

--- Engels - English ---

--
Groeten / Regards

Patrick Vanhoucke
Laken (Brussel)

Patrick Vanhoucke wrote:

> --- English - Engels ---
>
> In MySQL, the percent sign % is the wildcard. This wildcard
> represents one or more characters, i.e. differentiation between one
> or more characters isn't possible in a straightforward way. Is
> there a way to avoid this limitation? I would like to build a
> search interface that offers the possibility for the user to choose
> between a wildcard that represents exactly one character and a
> wildcard that represents one or more characters.
>
> --- Engels - English ---


You can use the _ underscore symbol to represent a single character while
using a like statement..


Select * from mytable where colname like '__';

would match any record of only 2 characters in length in this simple
example.

Another example, this time real results compared:


mysql> select * from lists where list_name like "Layt __ Class Staff";
+----+---------------------+-----------+
| id | list_name | list_user |
+----+---------------------+-----------+
| 94 | Jang MS Class Staff | fredys |
| 96 | Jang HS Class Staff | fredys |
+----+---------------------+-----------+
2 rows in set (0.00 sec)



mysql> select * from lists where list_name like "Jang % Class Staff";
+----+-----------------------+-----------+
| id | list_name | list_user |
+----+-----------------------+-----------+
| 91 | Jang Elem Class Staff | fredys |
| 94 | Jang MS Class Staff | fredys |
| 96 | Jang HS Class Staff | fredys |
+----+-----------------------+-----------+
3 rows in set (0.00 sec)

Patrick Vanhoucke wrote:
> In MySQL, the percent sign % is the wildcard. This wildcard represents
> one or more characters, i.e. differentiation between one or more
> characters isn't possible in a straightforward way. Is there a way to
> avoid this limitation? I would like to build a search interface that
> offers the possibility for the user to choose between a wildcard that
> represents exactly one character and a wildcard that represents one or
> more characters.

In SQL the % sign means zero or more characters. The _ sign means
exactly one character. These wildcards are used with the LIKE
comparison operator.

You can also use the REGEXP comparison operator. This offers more
flexible wildcards, using regular expressions.

See:
http://dev.mysql.com/doc/refman/5.0/...functions.html
http://dev.mysql.com/doc/refman/5.0/en/regexp.html

Regards,
Bill K.

Dixit Patrick Vanhoucke
in news:4398ca5b$0$31414$ba620e4c@news.skynet.be

> In MySQL, the percent sign % is the wildcard.
> This wildcard represents one or more characters,
> i.e. differentiation between one or more
> characters isn't possible in a straightforward
> way. Is there a way to avoid this limitation?
> I would like to build a search interface that
> offers the possibility for the user to choose
> between a wildcard that represents exactly one
> character and a wildcard that represents one or
> more characters.

Bill Karwin and Gazelem

Thank you so much for sending me the information about the
underscore as a wildcard that represents exactly one character.
Since I'm still a novice in these things, I overlooked this truly
simple solution.

I also thank Bill for sending me the information about wildcards
and regular expressions. This creates a lot of possibilities,
although in my opinion you can't offer all of them 'as is' to an
end user (visitor of a website).

--
Kind Regards

Patrick Vanhoucke
Laken (Brussel)

Patrick Vanhoucke wrote:
> I also thank Bill for sending me the information about wildcards and
> regular expressions. This creates a lot of possibilities, although in my
> opinion you can't offer all of them 'as is' to an end user (visitor of a
> website).

I agree. Typical users are not comfortable with regular expressions, so
a user interface should never ask them to enter one. It's also a bad
security risk to allow them to type in code, and then use their input in
your SQL statements. A malicious users might be able to insert a string
that could cause damage to your database.

Instead, offer more specific user interfaces (drop-down lists, radio
buttons, etc.) for all the options you do want them to choose, and then
your application builds a regular expression as a string, based on the
user's input to these other controls.

Regards,
Bill K.