On Mar 15, 2012, at 19:09, Doug Gorley <dgor...@aihs.ca> wrote: > G'day, > > I believe I've got some bad data in a table, but I'm not sure how it got > there, or how this scenario is possible. > > The table is called tdt_unsent. The field is str_name_l. For demonstration > purposes, the value is "SMITH". > > "select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows. > "select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows. > "select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows. > "select length(str_name_l) from tdt_unsent where str_name_l ~ '^SMITH'" > returns "5". > > So, it's as if there is a zero-length character at the end of the value that > is preventing a match. Is this possible? If so, how could this data have > been created? > > Thanks, > > Doug Gorley > dgor...@aihs.ca >
Try (in the regexp) adding '\r?\n' after SMITH and see what happens. How did you enter the SMITH record into the table in the first place? David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
