Thread: Comparing text field
I am trying to compare a large string (that has new line characters in it) to the contents of the text field (which also has new line characters in it) and it is not behaving as expected. For example I have the following record in the database: id=83 message=VAC153-683-685-131830- /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ BULLETIN - EAS ACTIVATION REQUESTED SEVERE THUNDERSTORM WARNING NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC 142 PM EDT THU APR 13 2006 THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A * SEVERE THUNDERSTORM WARNING If I do the following query SELECT id FROM table1 WHERE message='VAC153-683-685-131830- /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ BULLETIN - EAS ACTIVATION REQUESTED SEVERE THUNDERSTORM WARNING NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC 142 PM EDT THU APR 13 2006 THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A * SEVERE THUNDERSTORM WARNING' I get no results back, even though the message is equivalent. How should I perform this comparison so that the above query returns id=83? Thanks.
On 13 Apr 2006 12:20:08 -0700, Nik <XLPizza@gmail.com> wrote: > I am trying to compare a large string (that has new line characters in > it) to the contents of the text field (which also has new line > characters in it) and it is not behaving as expected. > > For example I have the following record in the database: > id=83 > message=VAC153-683-685-131830- > /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ > BULLETIN - EAS ACTIVATION REQUESTED > SEVERE THUNDERSTORM WARNING > NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC > 142 PM EDT THU APR 13 2006 > THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A > * SEVERE THUNDERSTORM WARNING > > If I do the following query > SELECT id FROM table1 WHERE message='VAC153-683-685-131830- > /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ > BULLETIN - EAS ACTIVATION REQUESTED > SEVERE THUNDERSTORM WARNING > NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC > 142 PM EDT THU APR 13 2006 > THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A > * SEVERE THUNDERSTORM WARNING' > > I get no results back, even though the message is equivalent. How > should I perform this comparison so that the above query returns id=83? Could one have \r\n and the other have \n ? Are you doing the comparison in psql or through a language (php, ruby, python, other) ? -- Postgresql & php tutorials http://www.designmagick.com/
You have made a wrong mistake in your SQL quary..... Your quary should be look like this..... SELECT id FROM table1 WHERE message like '%VAC153-683-685-131830- /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ BULLETIN - EAS ACTIVATION REQUESTED SEVERE THUNDERSTORM WARNING NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC 142 PM EDT THU APR 13 2006 THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A * SEVERE THUNDERSTORM WARNING'; --- Nik <XLPizza@gmail.com> wrote: > I am trying to compare a large string (that has new line characters > in > it) to the contents of the text field (which also has new line > characters in it) and it is not behaving as expected. > > For example I have the following record in the database: > id=83 > message=VAC153-683-685-131830- > /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ > BULLETIN - EAS ACTIVATION REQUESTED > SEVERE THUNDERSTORM WARNING > NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC > 142 PM EDT THU APR 13 2006 > THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A > * SEVERE THUNDERSTORM WARNING > > If I do the following query > SELECT id FROM table1 WHERE message='VAC153-683-685-131830- > /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ > BULLETIN - EAS ACTIVATION REQUESTED > SEVERE THUNDERSTORM WARNING > NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC > 142 PM EDT THU APR 13 2006 > THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A > * SEVERE THUNDERSTORM WARNING' > > I get no results back, even though the message is equivalent. How > should I perform this comparison so that the above query returns > id=83? > > Thanks. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
It is possible that one had /r/n and the other just /n. I was doing the
comparison using psql.
The issue was not using LIKE and %.
I resolved the problem by removing chr(13) from both sides. So my
working query was had a where clause like this:
WHERE REPLACE(message, chr(13), '') = REPLACE('<long string>', chr(13),
'')