mysql translate 函数_Oracle & MySQL 常用函数

Oracle：

--concat(str1,str2)字符串拼接函数

select concat(t.uuid,t.name) from group t;

select t.uuid || t.name from group t;

--initcap(str)将每个单词首字母大写，其他字母小写

select initcap(t.worker_code) from worker t;

select initcap(id) from table t;

--instr(x,find_string[,start][,occurrence])返回指定字符串在某字符串中的位置，可以指定搜索的开始位置和返回第几次搜索出来的结果,不存在时候返回0

select instr(t.worker_id,0),t.worker_id from worker t;--从1开始查找worker_id中0第1次出现的位置

select instr(t.worker_id,0,3), t.worker_id from worker t;--从3开始查找worker_id中0第1次出现的位置

select instr(t.worker_id,0,3,2), t.worker_id from worker t;--从3开始查找worker_id中0第2次出现的位置

--length(str)返回表达式中的字符数

select length(t.worker_code),t.worker_code from worker t;

--lengthb(str)返回表达式中的字节数

select lengthb(t.worker_code),t.worker_code, lengthb(t.worker_name),t.worker_name from worker t;

select lower(t.id),t.id from table t;

select upper(t.id),t.id from table t;

select lpad(t.worker_name,20),t.worker_name from worker t;

select lpad(t.worker_name,20,'*'),t.worker_name from worker t;

select rpad(t.worker_name,20),t.worker_name from worker t;

select rpad(t.worker_name,20,'*'),t.worker_name from worker t;

select trim(t.worker_id),t.worker_id from worker t;

select ltrim(t.worker_id,'0'),t.worker_id from worker t;

select rtrim(t.worker_id,'3'),t.worker_id from worker t;

--两边都去掉 截取集只能有一个字符

select trim('0' from '00000asd0000') from dual;--asd

--去左边

select trim(leading '0' from '00000asd0000') from dual;--asd0000

--去右边

select trim(trailing '0' from '00000asd0000') from dual;--00000asd

--去左右空格

select trim(' 00000asd0000 ') from dual;

select nvl(t.id,'0') from worker t;--若为空显示为0，不为空显示本身的值

select nvl2(t.id,'不为空','为空') from worker t;--不为空显示'不为空',为空显示'为空'

select t.worker_code, t.worker_name,case when t.id is null then 0 else 1 end as id from worker t where t.id is null;

select replace('0123456012',0,222) from dual;

select replace('jennifer','n','m') from dual;

select substr('123456',1) from dual;--从第一位开始到最后

select substr('123456',2,4) from dual;--从第二位开始数四个

select substr('123456',-2) from dual;--负数从右往左数，2位

select substr('123456',-3,2) from dual;--负数从右往左数后三位的前两位

select abs(-12) from dual;--绝对值

select ceil(123.23) from dual;--向上取整

select floor(123.78) from dual;--向下取整

select round(123.89) from dual;--四舍五入

select trunc(123.456,2) from dual;--截取两位小数，若不写，则取整 123.45,若为-1，则截取到十位，120.

select to_number('123.45') from dual;

select to_char(123.234) from dual;

select to_date('2012-3-15','YYYY-MM-DD') from dual;

select cast(12345.67 as varchar2(8)),cast('12-7月-07' as date), cast(12345.678 as number(7,2)) from dual;

--7为有效位，2为小数位，所以5就是整数位 '12-7月-07' 月字要写

select ascii('a') from dual;--97

select ascii('abc') from dual;--97

select ascii('') from dual;-- oracle :null mysql :0

--translate Oracle函数

SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual; --123456ghij

select translate('abcdrfg123','abcdrfg','') from dual;-- null 第三个表达式为空或者空字符串都解释为null，translate中任何参数为null结果都为null

select translate('abcdefg','ab','12') from dual;--12cdefg a对应1b对应2进行替换

select translate('abcbbaadef','abc','12 ') from dual;--#@ @@##def a对应1b对应2c对应空格 进行替换

select translate('abcbbaadef','abc','12') from dual;--#@ @@##def a对应1b对应2c删除掉 进行替换

Mysql:

select ifnull(t.id,0) from worker t where t.id is null;

--若起的别名如 id 有空格，点.等字符，如 t.id 需要加双引号 "t.id"

select t.worker_code, t.worker_name,case when t.id is null then 0 else 1 end as id from worker t;

select ifnull(t.id,0) from worker t where t.id is null;

select t.worker_code, t.worker_name,case when t.id is null then 0 else 1 end as id from worker t;

-- 非空显示原值，null显示0

select ifnull(t.id,0) from worker t;

--非空显示1，null显示0

select ifnull(isnull(t.id),0) from worker t;

--负数返回-1,0返回0，正数返回1

select sign(12.34) from dual;

select ascii('a') from dual;

select conv('a',16,2) from dual;--把十六进制的'a'转化为二进制

select bin(12) from dual;--1100

select oct(12) from dual;--14

select hex(12) from dual;--C

select char(77,121,83,81,'76');

select char(77,121,83,81,76);

select char(109,109,109);

select char(109,77.3,'77.3');

select locate('e','jennifer',3);--7

select left('jennifer',3);--jen 返回字符串左端3个字符

select right('jennifer',3);--fer 返回字符串右端3个字符

select length(space(8));--返回8个空格组成的一个字符串

select repeat('a',4); --返回4个a连成的字符串

select repeat('wer',-1);--返回空字符串

select repeat(12,3);--121212

select repeat(12,-2);--BLOB

select truncate(123.23,1); -- Oracle是trunc()函数