SQL 选择列以 \ 开头的位置 [英] SQL select where column begins with \

问题描述

我试图在 ColumnX 中查找数据以 \ 开头的所有数据.
like '\%' 是我要找的吗?但是 \ 必须在开头.

I'm trying to find all the data in ColumnX where the Data begins with a \.
Is like '\%' what I'm looking for? But the \ has to be at the beginning.

推荐答案

反斜杠 \ 在 LIKE 模式中可以有特殊含义.它是 PostgreSQL 中的默认 ESCAPE 字符 或 MySQL- 但不在 Oracle 或 SQL Server.(可以通过添加的 ESCAPE 子句以细微变化的方式进行修改，请点击 RDBMS 的链接.)

The backslash \ can have special meaning in LIKE patterns. It's the default ESCAPE character in PostgreSQL or MySQL - but not in Oracle or SQL Server. (Can be modified with an added ESCAPE clause in subtly varying ways, follow link for your RDBMS.)

除此之外，PostgreSQL 在 9.1 版和 MySQL 在 8.0 版中仍然如此).在那里你必须将 \ 加倍到 \\ 以获得一个普通的反斜杠.

In addition to that, PostgreSQL used to interpret POSIX-style escapes in strings (a.k.a. "C escape syntax") before version 9.1 and MySQL still does in version 8.0). There you have to double \ to \\ to get an ordinary backslash.

根据标准 SQL，\不是字符串中的元字符.PostgreSQL 最终切换到标准行为并引入了一种特殊的转义字符串语法，带有 E'' 和配置设置 standard_conforming_strings 和 escape_string_warning 在需要时仍允许转义字符串.

According to standard SQL, \ is not a meta character in strings. PostgreSQL eventually switched to standard behavior and introduced a special escape-string-syntax with E'' and the config settings standard_conforming_strings and escape_string_warning to still allow escaped string when needed.

自 Postgres 9.1 standard_conforming_strings = on 默认情况下.所以你写:

Since Postgres 9.1 standard_conforming_strings = on by default. So you write:

... col LIKE '\\%'    -- double once to get 1 literal backslash in LIKE pattern

否则你必须写:

... col LIKE E'\\\\%'  -- double twice to also disable special meaning as escape char

<小时>

除此之外，\ 在许多客户端程序和编程语言中还具有特殊含义.如果字符串在到达数据库引擎之前就被解释了，您可能需要再次将 \ 加倍(或以其他方式对其进行转义).例如，请参阅此相关问题.

---

On top of that, \ also has special meaning in many client programs and programming languages. If strings get interpreted before they even reach the database engine, you may have to double the \ another time (or escape it in some other fashion). See this related question, for instance.

这篇关于SQL 选择列以 \ 开头的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！