Re: Zero-length character breaking query?

On Mar 15, 2012, at 19:09, Doug Gorley  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@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.