时间戳

首页 > 解决方案 > Does a new line character affect Postgres LIKE text search?

postgresql - Does a new line character affect Postgres LIKE text search?

问题描述

So I have the following stored in a text column in Postgres...

This is a sentence.\nAnd here we start a new sentence.\nThen finally we have a third sentence.

Will those \n affect a LIKE query on that column?

i.e. SELECT "tracks".* FROM "tracks" WHERE (info LIKE '%sentence%')

The reason I ask is I want the line breaks for properly formatting the output of the text, but obviously don't want them screwing up search.

标签： postgresql

解决方案

只要您将换行符存储为换行符，它就不会弄乱您的查询。

CREATE TABLE example(
   id serial PRIMARY KEY,
   text TEXT NOT NULL
);

INSERT INTO example (text) VALUES (E'This is a sentence.\nAnd here we start a new sentence.\nThen finally we have a third sentence.')

SELECT * FROM example WHERE text LIKE '%nAnd%'; -- 0 records returned
SELECT * FROM example WHERE text LIKE '%And%'; -- 1 record returned

E当我将它插入表格时，请注意字符串之前的内容。

推荐阅读