[GENERAL] Regex problems
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:
dept |last |first
--------+--------------------+-------
A5A0 |Achey |Karen
A5M2 |Achey |Lonny
A75P |Ackley |Roger
A847 |Adair |Ethel
A84P |Adams |Denise
A654 |Adams |Gary
...
A847 |Young |Ronald
A75P |Younger |Curtis
A847 |Zappia |Sandy
A850 |Zrinski |Beverly
A5MP |Zygmant |Michael
(883 rows)
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
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
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
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"
or
"^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
Import Notes
Resolved by subject fallback