SQL查询中双波浪号~~作用及Oracle、Teradata等效写法咨询

• 现有两张业务表结构及样例数据：
  • 第一张表（Table_1）：包含Line_Card字段，存储的样例值为：0-17、0-10、1-1、0-11、1-1-17、1-1-10、1-1-13、1-1-6、1-1-21
  • 第二张表（Table_2）：包含Port字段，存储的样例值为：0-13-37、1-1-2-40、0-8-29、1-1-4-12、1-1-2-14、0-1-30、0-17-25、1-1-17-62、1-1-1-55
• 原有左关联查询SQL（PostgreSQL环境下编写，Oracle执行报无效字符错误）：

SELECT Line_Card, Port
FROM Table_1
LEFT JOIN Table_2 ON
Table_1.Column= Table_2.Column
             AND Table_1.Port ~~ LIKE_ESCAPE((Table_2.Line_Card || '-%'),'\')
             AND Table_1.Port !~~ LIKE_ESCAPE((Table_2.Line_card || '-%-%'),'\');

• 待解决问题：
  1. SQL中双波浪号（~~）的作用是什么
  2. 提供该语句在Oracle、Teradata环境下可正常运行的等效写法

1. 双波浪号（~~）的作用 #

~~是PostgreSQL独有的非标准SQL运算符，是大小写敏感LIKE匹配的简写别名，等效于标准SQL的LIKE；对应的!~~是NOT LIKE的简写别名，作用是判断字符串不匹配指定模式。
原SQL里的LIKE_ESCAPE(pattern, escape_char)也是PostgreSQL的内置函数，等效于标准SQL中LIKE pattern ESCAPE escape_char的写法，作用是指定模式匹配的转义符，这里指定反斜杠\为转义符，用来匹配通配符%/_本身（当前样例数据中无通配符，转义符实际不生效）。

注：原SQL存在字段归属笔误：按照表结构定义，Line_Card属于左表Table_1（短层级编码），Port属于右表Table_2（长层级编码），匹配逻辑应为Port匹配Line_Card的层级前缀，原SQL写反了两个字段的表前缀，后续等效写法已修正该笔误。

原SQL的实际匹配逻辑：两表公共关联字段（即SQL中同名的Column字段）值相等的前提下，Port恰好是Line_Card的直接下级编码：即Port以Line_Card || '-'为前缀，且前缀后的剩余内容中不再包含-，按-分割层级时，Port的层级数刚好比Line_Card多1，不会出现跨多级匹配。

2. 不同数据库的等效实现 #

Oracle 环境写法 #

Oracle直接支持ANSI标准的LIKE ... ESCAPE ...语法，没有~~运算符和LIKE_ESCAPE函数，直接替换为标准语法即可：

SELECT t1.Line_Card, t2.Port
FROM Table_1 t1
LEFT JOIN Table_2 t2 
  ON t1.Column = t2.Column
  AND t2.Port LIKE t1.Line_Card || '-%' ESCAPE '\'
  AND t2.Port NOT LIKE t1.Line_Card || '-%-%' ESCAPE '\';

Teradata 环境写法 #

Teradata同样兼容ANSI标准的LIKE匹配语法，字符串拼接规则、ESCAPE子句用法和Oracle完全一致，等效写法如下：

SELECT t1.Line_Card, t2.Port
FROM Table_1 t1
LEFT JOIN Table_2 t2 
  ON t1.Column = t2.Column
  AND t2.Port LIKE t1.Line_Card || '-%' ESCAPE '\'
  AND t2.Port NOT LIKE t1.Line_Card || '-%-%' ESCAPE '\';

说明：如果确定Line_Card字段值中不会出现%、_这两个通配符，上述两种写法里的ESCAPE '\'可以省略，不影响匹配结果。

内容的提问来源于stack exchange，提问作者Omar