Oracle常用函数

一.SUBSTR函数（字符截取函数）

格式1： substr(string string, int a, int b);
格式2：substr(string string, int a) ;

说明：
格式1：
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
3、b 要截取的字符串的长度

格式2：
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。

实例解析

   1、select substr('HelloWorld',0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
   2、select substr('HelloWorld',1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
   3、select substr('HelloWorld',2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符
   4、select substr('HelloWorld',0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
   5、select substr('HelloWorld',5,3) value from dual; //返回结果：oWo
   6、select substr('Hello World',5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
   7、select substr('HelloWorld',-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色 第三个注解）
   8、select substr('HelloWorld',-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色 第三个注解）
   9、select substr('HelloWorld',-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）
  10、select substr('HelloWorld',-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）

  11、select substr('HelloWorld',0) value from dual;  //返回结果：HelloWorld，截取所有字符
  12、select substr('HelloWorld',1) value from dual;  //返回结果：HelloWorld，截取所有字符
  13、select substr('HelloWorld',2) value from dual;  //返回结果：elloWorld，截取从“e”开始之后所有字符
  14、select substr('HelloWorld',3) value from dual;  //返回结果：lloWorld，截取从“l”开始之后所有字符
  15、select substr('HelloWorld',-1) value from dual;  //返回结果：d，从最后一个“d”开始 往回截取1个字符
  16、select substr('HelloWorld',-2) value from dual;  //返回结果：ld，从最后一个“d”开始 往回截取2个字符
  17、select substr('HelloWorld',-3) value from dual;  //返回结果：rld，从最后一个“d”开始 往回截取3个字符 

二.TRIM函数

trim（string）：去除指定字符串string的左右空格，当然，string中间有空格的时候是不会被去除的。
ltrim（string）、rtrim（string）：分别去除指定字符串string左侧和右侧的空格。

三.translate函数和replace函数

1.translate
语法：TRANSLATE(char, from, to)
用法：返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。若from比to字符串长，那么在from中比to中多出的字符将会被删除。三个参数中有一个是空，返回值也将是空值。

举例：SQL> select translate('abcdefga','abc','wo') 返回值 from dual;
返回值：wodefgw

分析：
该语句要将'abcdefga'中的'abc'转换为'wo',
由于'abc'中'a'对应'wo'中的'w',
故将'abcdefga'中的'a'全部转换成'w';
而'abc'中'b'对应'wo'中的'o',
故将'abcdefga'中的'b'全部转换成'o';
'abc'中的'c'在'wo'中没有与之对应的字符,
故将'abcdefga'中的'c'全部删除;
简单说来，就是将from中的字符转换为to中与之位置对应的字符，
若to中找不到与之对应的字符，返回值中的该字符将会被删除。
在实际的业务中，可以用来删除一些异常数据

2.replace

语法：REPLACE(char, search_string,replacement_string)
用法：将char中的字符串search_string全部转换为字符串replacement_string。

举例1：SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') 返回值 from dual;
返回值：fgsgswsgs

举例2：
SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') 返回值 from dual;
返回值:fgeeerrrtttsweeerrrttts

分析：
第一个例子中由于'fgsgswsgs'中没有与'fk'匹配的字符串，
故返回值仍然是'fgsgswsgs'；
第二个例子中将'fgsgswsgs'中的字符串'sg'全部转换为'eeerrrttt'。

总结：综上所述，replace与translate都是替代函数，
只不过replace针对的是字符串，而translate针对的是单个字符。

四.CAST函数

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。
语法：CAST (expression AS data_type)
expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED

五.COALESCE函数

COALESCE()函数 定义：返回列表中第一个非null表达式的值。如果所有表达式求值为null，则返回null。

COALESCE()函数有两种用法：
1.COALESCE ( expression1, expression2 );
2.COALESCE ( expression1, expression2, ... expression-n );

其中第一种就相当于Oracle中的nvl或者mysql中的ifnull，写成表达式的形式为：
CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE
expression2 END;

第二种可以包含n个表达式，表示如果第一个不为空取第一个，否则判断下一个，以此类推，如果全部为空，则返回null值。
需注意：vertica中空字符串跟null值不一样。