Oracle 数据库入门之----------------------单行函数

1，单行函数

SQL> --字符函数

SQL> select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello world') 首字母大写

  2  from dual;

转小写      转大写      首字母大写                                              

----------- ----------- -----------                                             

hello world HELLO WORLD Hello World                                             

SQL> --substr(a,b) 从a中，第b位开始取

SQL> select substr('Hello World',4) 子串 from dual;

子串                                                                            

--------                                                                        

lo World                                                                        

SQL> --substr(a,b,c) 从a中，第b位开始取，取c位

SQL> select substr('Hello World',4,3) 子串 from dual;

子                                                                              

---                                                                             

lo                                                                              

SQL> --length 字符数 lengthb 字节数

SQL> select length('Hello World') 字符,lengthb('Hello World') 字节 from dual;

      字符       字节                                                           

---------- ----------                                                           

        11         11                                                           

SQL> ed

已写入 file afiedt.buf

  1* select length('北京') 字符,lengthb('北京') 字节 from dual

SQL> /

      字符       字节                                                           

---------- ----------                                                           

         2          4                                                           

SQL> --instr(a,b)

SQL> --在a中，查找b

SQL> select instr('Hello World','ll') 位置 from dual;

      位置                                                                      

----------                                                                      

         3                                                                      

SQL> --lpad 左填充  rpad 右填充

SQL> -- abcd  ---> 10位

SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;

左         右                                                                   

---------- ----------                                                           

******abcd abcd******                                                           

SQL> --trim 去掉前后指定的字符

SQL> select trim('H' from 'Hello WorldH') from dual;

TRIM('H'FR                                                                      

----------                                                                      

ello World                                                                      

SQL> --replace

SQL> select replace('Hello World','l','*') from dual;

REPLACE('HE                                                                     

-----------                                                                     

He**o Wor*d                                                                     

SQL> host cls

SQL> --四舍五入

SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五

  2  from dual;

        一         二         三         四         五                          

---------- ---------- ---------- ---------- ----------                          

     45.93       45.9         46         50          0                          

SQL> --截断

SQL> ed

已写入 file afiedt.buf

  1  select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五

  2* from dual

SQL> /

        一         二         三         四         五