Comparing text field

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