Thread: [GENERAL] Regex problems

[GENERAL] Regex problems

From

Charles Curley

Date:

06 October 1998, 18:09:30

I'm running 6.3.2 on RedHat 5.0. I have the following table:

Table    = employees
+---------------------+----------------------------------+-------+
|    Field            |              Type                | Length|
+---------------------+----------------------------------+-------+
| id                  | char() not null                  |     8 |
| first               | char()                           |    20 |
| mi                  | char()                           |     1 |
| last                | char()                           |    20 |
| suffix              | char()                           |     8 |
| dept                | char()                           |     8 |
| phone               | char()                           |    20 |
| mp                  | char()                           |     8 |
| email               | char()                           |    64 |
+---------------------+----------------------------------+-------+

If I do the following:

select "dept", "last", "first" from "employees" where "last" = 'Curley';

I get my information. If I do:

select "dept", "last", "first" from "employees" where "last" ~ 'C*';

I get the entire table's worth of matches (883) regardless of what the last name is. Examples that are matched:

Trying the following:

select "dept", "last", "first" from "employees" where "last" ~ 'Cu*';

Produces fewer hits (74) but gives wrong values again, basically returning all the employees with a last name that begins with 'C' as well a few strange matches at the end with "last" values of 'Del Castillo', 'Moore', and 'O''Connor'.

Telling me to use LIKE isn't what I'm hoping to get here. My searches will require that I use regular expressions, I just toned them down for the examples.

--
Charles Curley, Staff Engineer
Computer Integrated Manufacturing
Lockheed Martin Ocala Operations

Re: [GENERAL] Regex problems

From

"chris yambo"

Date:

06 October 1998, 18:21:39

If you want to select all the employes whose last name begins with a C
you would use this regex '^C'.  The ^ signifys the beginning of the
string and you dont need a * after the C because a * means _zero_ or
more matches, and that is the problem you were having

chris yambo
thoughtbubble productions
http://www.thoughtbubble.com

Re: [GENERAL] Regex problems

From

Bruce Momjian

Date:

07 October 1998, 01:46:53

> select "dept", "last", "first" from "employees" where "last" ~ 'C*';

You want:

> select "dept", "last", "first" from "employees" where "last" ~ '^C';

or

> select "dept", "last", "first" from "employees" where "last" ~ '^C.*';

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [GENERAL] Regex problems

From

"James Oden"

Date:

07 October 1998, 10:25:02

I get my information. If I do:
select "dept", "last", "first" from "employees" where "last" ~ 'C*';
I get the entire table's worth of matches (883) regardless of what the last name is. Examples that are matched:

The answer is you need to use the meta character that tells the regx engine to look at only the begining of the string for a match. That is your regular expression should be:

"^C"

"^C*"

which yields a query like:

select "dept", "last", "first" from "employees" where "last" ~ "^C";

Adding the 'u' is not a bad idea if you want to get only the last names that start with "Cu".

...james