Oracle： --concat(str1,str2)字元串拼接函數 select concat(t.uuid,t.name) from group t; select t.uuid || t.name from group t;--initcap(str)將每個單詞首字母大寫，其他字母小寫sele ...
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()函數
