On Mar 15, 2012, at 19:09, Doug Gorley <dgorley@xxxxxxx> 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 > dgorley@xxxxxxx > 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
