Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

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.
0
ivanovn
Asked:
ivanovn
1 Solution
 
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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now