在PostgreSQL 9之前的版本中,可以直接使用反斜杠\进行转义;比如:\b表示退格, \n表示换行, \t表示水平制表符,\r标示回车,\f表示换页。除此之外还支持\digits和\xhexdigits,分别表示转义八进制和十六进制数据。
1.使用正则表达式
使用 PostgreSQL 的正则表达式函数 regexp_replace 来去除字符串中的 HTML 标签和特殊字符。
以下是一个示例 SQL 查询,它从一个名为 mytable 的表中选择一个名为 mycolumn 的文本列,并使用 regexp_replace 函数来去除其中的 HTML 标签和特殊字符,
该查询使用正则表达式 <[^>]*> 匹配所有 HTML 标签,并使用空字符串替换它们。然后它使用正则表达式 E’[\n\r]+’ 匹配所有换行符和回车符,并使用空格替换它们。最后一个参数 ‘g’ 表示全局匹配(替换所有匹配到的标签,而不是只替换第一个)。最后一个参数 ‘g’ 表示全局匹配(替换所有匹配到的标签,而不是只替换第一个)。
SELECT regexp_replace(regexp_replace(mycolumn, '<[^>]*>', '', 'g'), E'[\\n\\r]+', ' ', 'g') FROM mytable;
2.使用第三方扩展插件
CREATE EXTENSION IF NOT EXISTS pg_strip;
SELECT id, title, strip_tags(content) AS stripped_content
FROM article;
但是在PostgreSQL 9之后的版本,反斜杠已经变成了普通字符;如果想要使用反斜杠来转义字符,就必须在需要转义的字符串前面加上E(E就是Escape),如下:
换行符写法
SELECT E'test line 1\ntest line 2';
SELECT E'张\t小明';
SELECT 'test line 1' || E'\n' || 'test line 2';
SELECT * FROM USER WHERE username LIKE E'%\t%';
SELECT 'test line 1' || chr( 10 ) || 'test line 2';
SELECT * FROM 表名 WHERE strpos( 字段, chr( 10 ) ) > 0;
对单引号的两种转义方式
在SQL标准中字符串是用单引号括起来的,而在PostgreSQL中遵守了该标准,双引号则是用来表示变量的,如果在字符串中需要使用到单引号,就需要对其进行转义。
方式一:使用E和反斜杠进行转义
select E'\'233';
方式二:直接用一个单引号来转义单引号
select '''233';
这两种方式都能得到'233的结果而不会报错,第二种方式比较简单,也可以通过修改standard_conforming_strings参数的值来让反斜杠从普通字符变回转义字符:
查询并修改该参数的值:
show standard_conforming_strings;
SET standard_conforming_strings = on;
SET standard_conforming_strings = off;
当该参数的值为off时就可以直接使用反斜杠作为转义字符里,如下:
select '\'233';
将会得到'233的结果而不会报错。