3.2.2.6. 转换为 Tdsql-PostgreSQL
3.2.2.6.1. 字符串函数
3.2.2.6.1.1. SUBSTR
语法
SUBSTR(str, pos)
SUBSTR(str, pos, len)
- 描述
- 返回 str 的子字符串，起始位置为 pos，长度为 len。参数中包含 NULL 时，返回 NULL
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串，支持varchar2、char、clob、number(n,m)类型 |
pos |
子字符串的起始位置，支持varchar2、char、number(n,m)类型，处理之后范围是 4 字节（32 位）有符号整数，即 [-2147483648, 2147483647] |
len |
子字符串的长度，支持varchar2、char、number(n,m)类型，处理之后范围是 4 字节（32 位）有符号整数，即 [-2147483648, 2147483647] |
警告
Oracle 和 Tdsql-PostgreSQL 在数值的内部存储或显示格式上存在差异。如：对于数值 0.01，Oracle 在某些场景（如使用默认格式化规则时）可能将其显示为 .01，而 Tdsql-PostgreSQL 则保留前导 0，显示为 0.01；对于精度超过38位的数值和Char数据类型存储固定字符数的数值，两边数据库处理不一致。
substr转化的时候，会对参数进行强制转化，部分场景存在差异。例如，对于数值 10.00 Tdsql-PostgreSQL数据库中强制成text类型后，结果是 ‘10.00’,当这个值作为该函数的第一个参数，最终结果可能不一致。
示例
-- 转换前Oracle SQL：
SELECT SUBSTR('abcdefg', 3),SUBSTR('abcdefg', 3, 2),SUBSTR('abcdefg', -3),SUBSTR('abcdefg', 3, -2) FROM DUAL;
-------------------+---------------------+--------------------+----------------------+
SUBSTR('ABCDEFG',3)|SUBSTR('ABCDEFG',3,2)|SUBSTR('ABCDEFG',-3)|SUBSTR('ABCDEFG',3,-2)|
-------------------+---------------------+--------------------+----------------------+
cdefg |cd |efg | |
select substr (20250217,2,5), substr (2025.0217,1,6) from dual;
SUBSTR(20250217,2,5)|SUBSTR(2025.0217,1,6)|
--------------------+---------------------+
02502 |2025.0 |
-- 转换后Tdsql-PostgreSQL SQL：
SELECT unisql.SUBSTR(CAST('abcdefg' AS text), CAST(3 AS numeric)),unisql.SUBSTR(CAST('abcdefg' AS text), CAST(3 AS numeric), CAST(2 AS numeric)),
unisql.SUBSTR(CAST('abcdefg' AS text), CAST(-3 AS numeric)),unisql.SUBSTR(CAST('abcdefg' AS text), CAST(3 AS numeric), CAST(-2 AS numeric))
substr|substr|substr|substr|
------+------+------+------+
cdefg |cd |efg | |
SELECT unisql.substr(CAST(20250217 AS text), CAST(2 AS numeric), CAST(5 AS numeric)),unisql.substr(CAST(2025.0217 AS text), CAST(1 AS numeric), CAST(6 AS numeric))
substr|substr|
------+------+
02502 |2025.0|
3.2.2.6.1.2. REGEXP_INSTR
- 语法
REGEXP_INSTR(source_char, pattern [, position, occurrence, return_option ])- 描述
- 该函数作用是返回正则表达式匹配值在源字符串中的位置。
参数解释 +—————+———————————————————————————————————–+ | 参数 | 说明 | +===============+===========================================================================================================+ | source_char | 指定用作搜索值的字符表达式，数据类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB | +—————+———————————————————————————————————–+ | pattern | 指定正则表达式截取规则。它通常是一个文本文字，字符类型可为 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 | +—————+———————————————————————————————————–+ | position | 可选，指定从 source_char 哪个位置开始搜索字符串，是正整数，默认值是 1 | +—————+———————————————————————————————————–+ | occurrence | 可选，指定搜索第几次匹配 pattern。是正整数，默认值是 1 | +—————+———————————————————————————————————–+ | return_option | 可选，指定返回结果的情况，0，表示返回匹配的起始位置；1，表示返回匹配的结束位置，默认值为0，其余数值不支持 | +—————+———————————————————————————————————–+
注意
由于Tdsql-PostgreSQL中不存在regexp_instr函数,所以在使用统一SQL转换涉及到regexp_instr函数的sql时,需要先导入创建自定义函数的脚本(参考快速开始-》导入统一SQL脚本到目标库中一节)
1. 当前仅支持两到五个参数的转换
2. 当第三、四、五参数是''时,Oracle返回null,转换后的sql在Tdsql-PostgreSQL中报错,使用时请注意。
3. 正则匹配默认大小写敏感,多行被视为一行匹配,支持的正则规则参考后续列表
4. 由于Oracle和Tdsql-PostgreSQL关于正则的实现存在差异,会出现部分正则写法不支持的情况,例如:
在Oracle中:
select regexp_instr('IL3nbfhzGl','*') FROM dual;
REGEXP_INSTR('IL3NBFHZGL','*')|
------------------------------+
1|
转换之后在Tdsql-PostgreSQL模式数据库执行:
SELECT unisql.regexp_instr(CAST('IL3nbfhzGl' AS text), CAST('*' AS text))
SQL 错误 [2201B]: ERROR: invalid regular expression: quantifier operand invalid
在Oracle中:
SELECT REGEXP_INSTR('foofoo', '(foo)\1') FROM DUAL;
REGEXP_INSTR('FOOFOO','(FOO)\1')|
--------------------------------+
1|
转换之后在Tdsql-PostgreSQL模式数据库执行:
SELECT unisql.REGEXP_INSTR(CAST('foofoo' AS text), CAST('(foo)1' AS text))
SQL 错误 [2201B]: ERROR: invalid regular expression: invalid backreference number
示例
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR(' unisql1 unisql12 unisql128', 'unisql\d+') FROM dual;
REGEXP_INSTR('UNISQL1UNISQL12UNISQL128','UNISQL\D+')|
----------------------------------------------------+
2|
SELECT REGEXP_INSTR(' unisql1 unisql12 unisql128', 'unisql\d+',4) FROM dual;
REGEXP_INSTR('UNISQL1UNISQL12UNISQL128','UNISQL\D+',4)|
------------------------------------------------------+
10|
SELECT REGEXP_INSTR(' unisql1 unisql12 unisql128', 'unisql\d+',4,2) FROM dual;
REGEXP_INSTR('UNISQL1UNISQL12UNISQL128','UNISQL\D+',4,2)|
--------------------------------------------------------+
19|
SELECT REGEXP_INSTR(' unisql1 unisql12 unisql128', 'unisql\d+',4,2,1) FROM dual;
REGEXP_INSTR('UNISQL1UNISQL12UNISQL128','UNISQL\D+',4,2,1)|
----------------------------------------------------------+
28|
-- 转换后Tdsql-PostgreSQL模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST(' unisql1 unisql12 unisql128' AS text), CAST('unisql\d+' AS text));
regexp_instr|
------------+
2|
SELECT unisql.REGEXP_INSTR(CAST(' unisql1 unisql12 unisql128' AS text), CAST('unisql\d+' AS text), CAST(4 AS int));
regexp_instr|
------------+
10|
SELECT unisql.REGEXP_INSTR(CAST(' unisql1 unisql12 unisql128' AS text), CAST('unisql\d+' AS text), CAST(4 AS int), CAST(2 AS int));
regexp_instr|
------------+
19|
SELECT unisql.REGEXP_INSTR(CAST(' unisql1 unisql12 unisql128' AS text), CAST('unisql\d+' AS text), CAST(4 AS int), CAST(2 AS int), CAST(1 AS int))
regexp_instr|
------------+
28|
正则规则
规则 |
描述 |
备注 |
|---|---|---|
\ |
反斜杠(1.可代表自己;2.引用下一个字符;3.引入一个操作符;4.什么都不做) |
单独使用目标库执行会报错 |
* |
匹配零或更多 |
单独使用目标库执行会报错 |
+ |
匹配一次或多次 |
单独使用目标库执行会报错 |
? |
匹配零或一次 |
单独使用目标库执行会报错 |
| |
选择匹配,比如:aa`|`bb（匹配 aa 或 bb ） |
单独使用目标库执行不支持 |
^ |
默认情况下匹配字符串的开头。在多行模式下，它匹配源字符串中任何地方的任何行的开头。 |
单独使用目标库执行不支持 |
$ |
默认情况下匹配字符串的末端。在多行模式下，它匹配源字符串中任何地方的任何线路的末端。 |
单独使用目标库执行不支持 |
. |
匹配受支持的字符集中的任何字符,null除外 |
|
[ ] |
匹配列表中表示的任何表达式, 比如: [abc](匹配 a、b 或 c 中的一个字母),[a-z](匹配 a 到 z 中的一个字母)。如果使用^则表示匹配除列表外的表达式[^abc](匹配除了 a、b 或 c 中的其他字母) |
|
() |
分组表达 |
|
{m} |
完全匹配M次 |
|
{m,} |
匹配至少M次 |
|
{m,n} |
匹配至少M次，但不超过n次 |
|
\n |
反向表示表达式（n是1和9之间的数字）与n之前的“（’and”）’之间的第N个子表达相匹配 | |
|
[..] |
指定一个collation元素 |
|
[::] |
指定字符类（例如，[：alpha：]）。它匹配字符类中的任何字符 |
|
[==] |
指定等价类。例如，[= a =]匹配所有具有基本字母’a’的字符。 |
|
\d |
一个数字字符 |
|
\D |
一个非数字字符 |
|
\w |
一个单词字符 |
|
\W |
一个非单词字符 |
|
\s |
一个空格字符 |
|
\S |
一个非空格字符 |
|
\A |
仅在字符串的开头或字符串末端的newline字符之前进行匹配 |
单独使用目标库执行不支持 |
\Z |
仅在字符串结束时匹配 |
单独使用目标库执行不支持 |
*? |
匹配前面的模式元素0或更多次(nongreedy) |
单独使用目标库执行会报错 |
+? |
匹配前面的模式元素元素1或更多次(nongreedy) |
单独使用目标库执行会报错 |
?? |
匹配前面的模式元素0或1次(nongreedy) |
单独使用目标库执行会报错 |
{n}? |
完全匹配前面的模式元素n次(nongreedy) |
|
{n,}? |
与前面的模式元素匹配至少n次(nongreedy) |
|
{n,m}? |
匹配前面的模式元素，至少n，但不超过M次(nongreedy) |
|
()\n |
分组和后向引用 |
不支持 |
(?:expr) |
忽略捕获的子模式 |
不支持 |
(?=expr) |
正向先行断言（Positive Lookahead),它断言从当前位置开始，后面必须匹配表达式 |
不支持 |
(?!expr) |
负向先行断言（Negative Lookahead）,断言从当前位置开始，后面不能匹配表达式 |
不支持 |
\m |
只在一个词的开头匹配 |
不支持 |
\M |
只在一个词的末尾匹配 |
不支持 |
\y |
只在一个词的开头或末尾匹配 |
不支持 |
\Y |
只在一个词的不是开头或末尾的点上匹配 |
不支持 |
3.2.2.6.1.3. INSTR
- 语法
INSTR(string, substring [, position [, occurrence]])- 描述
- 该函数返回 substring 在 string 中的位置。参数中包含 NULL 时，返回 NULL当入参个数为2个时转换为strpos函数当入参个数为3个或者4个时，默认转换为unisql.instr当入参个数为其他情况，则透传
参数解释
参数 |
说明 |
|---|---|
string |
搜索的字符串 |
substring |
搜索的字符子串 |
position |
开始搜索的位置 |
occurrence |
匹配序号 |
警告
参数1和参数2只支持字符串类型，不支持其他类型。
当入参为CHAR(N)类型时，对于填充的空格字符串，oracle视为有效空格，postgres视为空串，因此会被识别为NULL值。
示例
-- 转换前Oracle SQL：
select instr('abcdefg','ab') from dual;
select instr('abcdefg','ab', 1) from dual;
select instr('abcdefg','ab', 1, 2) from dual;
-- 转换后Tdsql-PostgreSQL SQL：
SELECT strpos('abcdabcdefg', 'ab');
SELECT unisql.instr('abcdabcdefg', 'ab', 1);
SELECT unisql.instr('abcdabcdefg', 'ab', 1, 2)
3.2.2.6.1.4. SUBSTRING_INDEX
- 语法
SUBSTRING_INDEX (str, delimiter, count)- 描述
- 根据指定的分隔符 delimiter 和计数 count，从字符串 str 中提取子字符串。 若 count 为正数，则返回从字符串开头到第 count 个分隔符的所有内容； 若 count 为负数，则返回从字符串末尾到第 count 个分隔符的所有内容。 若 count 为0，则返回NULL。 若任一入参是NULL或’’，则返回NULL。
参数解释
参数 |
说明 |
|---|---|
str |
要处理的原始字符串 |
delimiter |
用于分割字符串的分隔符，必须为字符串类型 |
count |
整数，指定分隔符的计数。正数表示从开头计数，负数表示从末尾计数 |
警告
count不支持’’,Oracle里’’可以转为number。Tdsql-PostgreSQL不支持’’转为number。
入参仅支持字符串类型，以及可以隐式转换成字符串的类型如 NUMBER。
本函数 区分大小写
数据类型隐式转换存在差异会存在截取结果有差异。如char类型、日期类型等。 比如 SUBSTRING_INDEX(char_col, ‘,’, 2) ，char_col 为 ‘1,2,3’ ，但 char_col 类型的长度超过 5，而在 Oracle 中由于字符串会视为 ‘1,2,3 ‘ （带有空格补全）， Tdsql-PostgreSQL提取为1,2,3’。
示例
-- 转换前PostgreSQL SQL：
SELECT SUBSTRING_INDEX('www.example.com', '.', 2) from dual;
SUBSTRING_INDEX('www.example.com', '.', 2)|
-----------------------------------------+
www.example|
SELECT SUBSTRING_INDEX('www.example.com', '.', -2) from dual;
SUBSTRING_INDEX('www.example.com', '.', -2)|
------------------------------------------+
example.com|
-- 转换后 Tdsql-PostgreSQL SQL：
SELECT UNISQL.SUBSTRING_INDEX('www.example.com', '.', 2) ;
substring_index
-----------------
www.example
SELECT UNISQL.SUBSTRING_INDEX('www.example.com', '.', -2);
substring_index
-----------------
example.com
3.2.2.6.1.5. REGEXP_SUBSTR
语法
REGEXP_SUBSTR(source_char, pattern
[, position [, occurrence ] ])
- 描述
- 该函数允许使用正则表达式搜索字符串并返回匹配字符串
参数解释
参数 |
说明 |
|---|---|
source_char |
指定用作搜索值的字符表达式 |
pattern |
指定正则表达式截取规则。它通常是一个文本字面量 |
position |
指定开始正则表达式匹配的起始位置，取值是一个正整数，为可选项。默认值是 1，表示从第一个字符开始搜索 |
occurrence |
指定 source_char 中第几个正则表达式匹配位置，为可选项。默认值为 1 |
示例
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+') from dual;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+')|
---------------------------------------------+
a11 |
-- 转换后TDSQL-PostgreSQL：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1) FROM dual;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1)|
----------------------------------------------------------------------+
a11 |
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1) from dual;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+',1)|
-----------------------------------------------+
a11 |
-- 转换后TDSQL-PostgreSQL：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1) FROM dual;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1)|
----------------------------------------------------------------------+
a11 |
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1, 2) FROM DUAL;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+',1,2)|
-------------------------------------------------+
a22 |
-- 转换后TDSQL-PostgreSQL：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 2) FROM DUAL;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 2)|
----------------------------------------------------------------------+
a22 |
3.2.2.6.2. 时间日期函数
3.2.2.6.2.1. LAST_DAY
- 语法
LAST_DAY(date)- 描述
- 返回 date 当月最后一天的日期值
参数解释
参数 |
说明 |
|---|---|
date |
日期类型参数 |
示例
-- 转换前Oracle SQL：
SELECT LAST_DAY(SYSDATE),LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD')) FROM DUAL;
LAST_DAY(SYSDATE) |LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD'))|
-----------------------+--------------------------------------------+
2023-12-31 14:05:54.000| 2023-09-30 00:00:00.000|
-- 转换后Tdsql-PostgreSQL SQL：
SELECT unisql.last_day(statement_timestamp()),unisql.last_day(CAST(to_timestamp('2023/09/21', 'YYYY/MM/DD') AS timestamp))
last_day |last_day |
-----------------------+-----------------------+
2023-12-31 09:44:53.000|2023-09-30 00:00:00.000|
3.2.2.6.2.2. MONTHS_BETWEEN
- 语法
MONTHS_BETWEEN (date1，date2)- 描述
- 该函数是返回参数 date1 到 date2 之间的月数
参数解释
参数 |
说明 |
|---|---|
date1 |
DATE 数据类型的值。 |
date2 |
DATE 数据类型的值。 |
示例
-- 转换前Oracle SQL：
SELECT MONTHS_BETWEEN (TO_DATE('05-02-2023 13:24:52','MM-DD-YYYY HH24:MI:SS'), TO_DATE('03-04-2023 19:24:52','MM-DD-YYYY HH24:MI:SS') ) FROM dual;
MONTHS_BETWEEN(TO_DATE('05-02-202313:24:52','MM-DD-YYYYHH24:MI:SS'),TO_DATE('03-04-202319:24:52','MM-DD-YYYYHH24:MI:SS'))|
-------------------------------------------------------------------------------------------------------------------------+
1.92741935483870967741935483870967741935|
-- 转换后Tdsql-PostgreSQL SQL：
SELECT unisql.months_between(CAST(to_timestamp('05-02-2023 13:24:52', 'MM-DD-YYYY HH24:MI:SS') AS timestamp), CAST(to_timestamp('03-04-2023 19:24:52', 'MM-DD-YYYY HH24:MI:SS') AS timestamp))
months_between |
------------------+
1.9274193548387097|