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/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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),
'')