Professional Web Applications Themes

Wildcard in MySQL - MySQL

--- 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 ...

  1. #1

    Default Wildcard in MySQL

    --- 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 Guest

  2. #2

    Default Re: Wildcard in MySQL

    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:
    [url]http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/regexp.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Wildcard in MySQL

    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.



    Gazelem Guest

  4. #4

    Default Re: Wildcard in MySQL

    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)


    Gazelem Guest

  5. #5

    Default Re: Wildcard in MySQL

    Dixit Patrick Vanhoucke
    in news:4398ca5b$0$31414$ba620e4cnews.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 Guest

  6. #6

    Default Re: Wildcard in MySQL

    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.
    Bill Karwin Guest

Similar Threads

  1. Need help with wildcard
    By 4lua in forum Macromedia ColdFusion
    Replies: 2
    Last Post: June 14th, 09:32 PM
  2. wildcard in SELECT
    By ranger in forum Coldfusion Database Access
    Replies: 11
    Last Post: April 1st, 05:10 PM
  3. php replace wildcard
    By John The Builder in forum PHP Development
    Replies: 2
    Last Post: July 18th, 11:36 PM
  4. Is there any value in ID's Wildcard S&R ?
    By M_Blackburn@adobeforums.com in forum Adobe Indesign Windows
    Replies: 10
    Last Post: July 15th, 01:34 PM
  5. Wildcard and DTS..?
    By Kent Johnson in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 1st, 10:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139