MySQL和Oracle字符串截取函数用法总结(比较)

我的原创地址:https://dongkelun.com/2019/05/28/mysqlAndOracleSubString/

前言

本文总结MySQL和Oracle的字符串截取函数的用法

工作中MySQL和Oracle都用,有时会碰到两种数据库SQL用法的不同,就会上网查一下,但是时间久了,就忘记了,好记性不如烂笔头,所以写个笔记备忘一下~

1、MySql

函数:SUBSTRING 或 SUBSTR

1.1 语法

位置

SUBSTRING(string,position);
SUBSTRING(string FROM position);

位置和长度

SUBSTRING(string,position,length);
SUBSTRING(string FROM position FOR length);

1.2 下标

  • | H | e | l | l | 0 | | W | o | r | l | d
  • | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-:
    正数 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11
    负数 | -11 | -10 | -9 | -8 | -7 | -6 | -5 | -4 | -3 | -2 | -1

1.3 示例详解

1.3.1 位置

position>0,从position(包含)开始

SELECT SUBSTRING('Hello World',1);
SELECT SUBSTRING('Hello World' FROM 7);
Hello World
World

position=0返回空

SELECT SUBSTRING('Hello World',0);
 

position<0,与position为正时是一样的,下面的sql的效果是相同的

SELECT SUBSTRING('Hello World',-11);
SELECT SUBSTRING('Hello World' FROM -5);

当position的绝对值>LENGTH(string)时,返回空,和position=0时一样

SELECT SUBSTRING('Hello World',12);
SELECT SUBSTRING('Hello World',-12);

1.3.2 位置和长度

position的用法和上面讲的是一样的,下面仅总结length
length>0时返回length个字符数,当length>string的可截取的长度时,只返回可截取的长度

SELECT SUBSTRING('Hello World',1,5);
SELECT SUBSTRING('Hello World',6,20);
Hello
World

length<=0时返回空

SELECT SUBSTRING('Hello World',1,0);
SELECT SUBSTRING('Hello World',1,-20);
 

下面等价

SELECT SUBSTRING('Hello World',6,20);
SELECT SUBSTRING('Hello World' FROM 6 FOR 20);

可通过LENGTH查看字符串的长度验证(当length>string的可截取的长度时)

SELECT LENGTH(SUBSTRING('Hello World' FROM 6 FOR 20));
6

2、Oracle

函数:SUBSTR

和MySql不同的是没有SUBSTRING

2.1 语法

位置

SUBSTR(string,position);
SUBSTR(string FROM position);

位置和长度

SUBSTR(string,position,length);
SUBSTR(string FROM position FOR length);

2.2 下标

  • | H | e | l | l | 0 | | W | o | r | l | d
  • | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-:
    正数 | 0或1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11
    负数 | -11 | -10 | -9 | -8 | -7 | -6 | -5 | -4 | -3 | -2 | -1

2.3 示例详解

2.3.1 位置

与MySQL一样,position>0和position<0时是一样的效果,参照上面的下标对应即可,不同的是,position=0和position=1的效果是一样的。
下面三个sql效果一样

SELECT SUBSTR('Hello World',0) FROM DUAL;
SELECT SUBSTR('Hello World',1) FROM DUAL;
SELECT SUBSTR('Hello World',-11) FROM DUAL;
Hello World

当position的绝对值>LENGTH(string)时,返回[NULL]

SELECT SUBSTR('Hello World',12) FROM DUAL
SELECT SUBSTR('Hello World',-12) FROM DUAL;
[NULL]

2.3.2 位置和长度

position的用法和上面讲的是一样的,下面仅总结length
length>0时返回length个字符数,当length>string的可截取的长度时,只返回可截取的长度,这点和MySQL相同

SELECT SUBSTR('Hello World',1,5) FROM DUAL;
SELECT SUBSTR('Hello World',6,20) FROM DUAL;
Hello
World

length<=0时返回[NULL],这点和MySQL不同

SELECT SUBSTR('Hello World',1,0) FROM DUAL;
SELECT SUBSTR('Hello World',6,-20) FROM DUAL;
 [NULL]

3 比较总结

最后比较一下MySQL和Oracle的不同

  • 1、 MySQL函数为SUBSTRING 或 SUBSTR,Oracle只有SUBSTR
  • 2、 position=0时MySQL返回空,而Oracle和position=1时一样
  • 3、 当position的绝对值>LENGTH(string)时和length<=0时,MySQL返回空,而Oracle返回[NULL]

推荐阅读更多精彩内容