PostgreSql + Query Statement having \r in between the attributes !!!

Question

Suppose we have a textarea in which we put example string. Textbox contain :

Earth is revolving around the Sun.

But at the time of saving, I just pressed a enter key after "the sun". Now the statements in texbox ::

Earth is revolving around
 the Sun

Now, in database where enter was pressed the \r is stored. Now i am trying to fetch the data but unable, because my query is of such type ::

SELECT * FROM "volume_factors" WHERE lower(volume_notes) like E'atest\\r\\n 100'

Actual data stored in database field

atest\r
100

Please suggest me to solve the issue.I have tried gsub to replace but no change.

search_text_array[1] = search_text_array[1].gsub('\\r\\n','\r\n')

Thanks in Advance !!!

Answer 1

Try this:

update volume_factors set volume_notes = regexp_replace(volume_notes, '\r\n', ' ');

That's to replace crlf with one space for data that is already in the database. You use postgresql's psql to do this.

To prevent new data containing crlf entering database, you should do it in the application. If you use ruby's gsub, do not use single quote, use double quote to recognize \n like this:

thestring.gsub("\n", " ")

Answer 2

Here we can replace \r\n by % to fetch the data.

Seaching Query will be like this ::

SELECT * FROM "volume_factors" WHERE lower(volume_notes) like E'atest% 100'

gsub function ::

search_text_array[1] = search_text_array[1].gsub('\\r\\n','%')