Text field comparison involving new line characters

I have a text field that stores a chunk of text that contains some new lines as well. I am unable to do a succesful comparison of that field with provided string.

For example I have the following situation:

CREATE TABLE table1
(
  id int4 NOT NULL,
  message text,
);

In that table there is a record where:
id=1
message=SPECIAL WEATHER STATEMENT
NATIONAL WEATHER SERVICE BLACKSBURG VA
436 AM EDT FRI APR 14 2006
NCZ001>006-018>020-VAZ007-009>020-022>024-032>035-043>047-058-059-
WVZ042>045-141600

I want to find all the ids that have that particular message. So I execute the following query:

SELECT id FROM table1 WHERE message = 'SPECIAL WEATHER STATEMENT
NATIONAL WEATHER SERVICE BLACKSBURG VA
436 AM EDT FRI APR 14 2006
NCZ001>006-018>020-VAZ007-009>020-022>024-032>035-043>047-058-059-
WVZ042>045-141600'

However this returns no records. Why is this the case, and how should I compare this message to get a match on the id?

Thanks.
LVL 10
ivanovnAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

m1tk4Commented:
Most likely the value in the table contains \r\n's as line breaks (Windows) and the one you are comparing it to \n' s (Unix), or vice versa. Try comparing this way:

where replace(message, chr(13), '') = replace('<your long value here>', chr(13), '')

You may also want to add a trim() for both parts of the comparison on top of that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.