Professional Web Applications Themes

Newbie REGEXP question - MySQL

Hope it's OK for me to post a newbie question here. (mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using readline 5.1) I'd like to SELECT all Names that are composed of exactly two "alphabetical" words: "John Smith" but not "John Elias Smith". SELECT Names FROM MyTable WHERE Names REGEXP '[[:alpha:]]+[[:blank:]] {1}[[:alpha:]]+$'; This "almost" works but does also return some instances with more than two names. I would answer the same question if I could search for all Names in which there was only one space. Similarly, SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}'; returns multiple names like "John ...

  1. #1

    Default Newbie REGEXP question

    Hope it's OK for me to post a newbie question here.

    (mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using
    readline 5.1)

    I'd like to SELECT all Names that are composed of exactly two
    "alphabetical" words: "John Smith" but not "John Elias Smith".

    SELECT Names FROM MyTable WHERE Names REGEXP '[[:alpha:]]+[[:blank:]]
    {1}[[:alpha:]]+$';

    This "almost" works but does also return some instances with more than
    two names.

    I would answer the same question if I could search for all Names in
    which there was only one space. Similarly,

    SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}';

    returns multiple names like "John Elias Smith".

    Would you have the time to comment on this?

    Best, JA

    jonas.andersson@rocketmail.com Guest

  2. #2

    Default Re: Newbie REGEXP question

    On 22 Feb, 12:38, com wrote: 

    For 2 names:
    SELECT *
    FROM `namestab`
    WHERE `names` REGEXP '^[A-Za-z]+[[:space:]]+[A-Za-z]+$'

    For 3 names:
    SELECT *
    FROM `namestab`
    WHERE `names` REGEXP '^[A-Za-z]+[[:space:]]+[A-Za-z]+[[:space:]]+[A-Za-
    z]+$'

    Captain Guest

  3. #3

    Default Re: Newbie REGEXP question

    On 22 Feb, 12:38, com wrote: 

    Also, in your one, you have simply missed off the ^ signifying the
    beginning of the field thus:
    SELECT Names FROM MyTable WHERE Names REGEXP '^[[:alpha:]]+[[:blank:]]
    {1}[[:alpha:]]+$';

    Captain Guest

  4. #4

    Default Re: Newbie REGEXP question

    On 22 Feb 2007 04:38:54 -0800, com wrote: 

    It's finding the SECOND space in the column. You need to bind it at the
    beginning as well as the end, which you did with the '$' character.
     

    SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}' and
    Names NOT REGEXP '[[:blank:]]{1}.*[[:blank:]]{1}';

    Your version says "pick everything where there's a space in". This one
    says "pick everything where there's a space in and not two spaces in."

    --
    I love ASR, you have total freedom of speech as long as it's punctuated
    correctly. -- Chris Hacking in a.s.r
    Peter Guest

  5. #5

    Default Re: Newbie REGEXP question

    On Feb 22, 2:16 pm, "Peter H. Coffin" <com> wrote: 




    >
    > It's finding the SECOND space in the column. You need to bind it at the
    > beginning as well as the end, which you did with the '$' character.



    >
    > SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}' and
    > Names NOT REGEXP '[[:blank:]]{1}.*[[:blank:]]{1}';
    >
    > Your version says "pick everything where there's a space in". This one
    > says "pick everything where there's a space in and not two spaces in."
    >
    > --
    > I love ASR, you have total freedom of speech as long as it's punctuated
    > correctly. -- Chris Hacking in a.s.r[/ref]

    SELECT `names`
    FROM `mytable`
    WHERE TRIM( `names` ) LIKE "% %"
    AND TRIM( `names` ) NOT LIKE "% % %";

    strawberry Guest

  6. #6

    Default Re: Newbie REGEXP question

     

    Many thanks to all of you for helping me with this one! You guys are
    great!

    Best, JA

    jonas.andersson@rocketmail.com Guest

  7. #7

    Default Re: Newbie REGEXP question

    On 22 Feb, 15:22, com wrote: 
    >
    > Many thanks to all of you for helping me with this one! You guys are
    > great!
    >
    > Best, JA[/ref]

    Aw shucks ... !blush!

    Captain Guest

  8. #8

    Default Re: Newbie REGEXP question

    On 22 Feb 2007 06:05:37 -0800, Captain Paralytic wrote: 
    >
    > For 2 names:
    > SELECT *
    > FROM `namestab`
    > WHERE `names` REGEXP '^[A-Za-z]+[[:space:]]+[A-Za-z]+$'[/ref]

    Are all letters in those [A-Za-z] ranges? αλτρ

    --
    For every subject you can think of there are at least 3 web sites.
    The owners of these web sites know each other and at least one of
    them hates at least one of the others.
    -- mnlooney's view of Skif's Internet Theorem
    Peter Guest

  9. #9

    Default Re: Newbie REGEXP question

    On 22 Feb, 16:03, "Peter H. Coffin" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Are all letters in those [A-Za-z] ranges? αλτρ
    >
    > --
    > For every subject you can think of there are at least 3 web sites.
    > The owners of these web sites know each other and at least one of
    > them hates at least one of the others.
    > -- mnlooney's view of Skif's Internet Theorem- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    No, but I doubt if any names will not contain at least one of them.

    Captain Guest

Similar Threads

  1. Perl newbie: regexp
    By Raj in forum PERL Beginners
    Replies: 3
    Last Post: June 17th, 03:33 PM
  2. regexp question + html::pr question on the side
    By boris in forum PERL Miscellaneous
    Replies: 4
    Last Post: September 27th, 02:24 AM
  3. [newbie] string pattern instead of regexp
    By Yvon Thoraval in forum Ruby
    Replies: 3
    Last Post: September 22nd, 06:27 AM
  4. regexp question
    By Bart van den Burg in forum PHP Development
    Replies: 7
    Last Post: September 17th, 08:11 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