orcal 数据库学习笔记(八)（SQL简单函数）

(1)找出“orcal traning”第二个ra 出现的位置

 

Select instr('orcal traning','ra',1,2) from dual;

<span style="font-family: 宋体;">(2)单记录函数(1)ASCII 返回与特定的字符串对应的十进制数</span>

<span style="font-family: 宋体;">select ascii('A')A; ascii('a')a,ascii('o')zero from dual;</span>

<span style="font-family: 宋体;">A65  a97 zero 48</span>

 

(3)chr,给出整数 返回对应的字符

 

select chr(54748)zhao,chr(65)chr65 from dual;

(4) concat 连接两个字符串

 

 

select concat('010-','8888')||'转23' 高某电话 from dual;
高某电话  010-8888转23
select concat(ename,'是好人')from emp;  scott是好人

 concatenate与11的作用相同

 

(5)initcap 返回字符串，并将字符串第一个字母变为大写

 

select initcap('smith')upp from dual;  Smith

(6)返回字符串长度

 

 

select length('XXX') from dual;
select name,length(name),address,length(addr) from XX where length(ename)=4;

(7)lower 将字符串小写

 

 

select lower('AaBb')AaBb from dual;
 select lower(ename),upper(ename) from emp;

(8)取字符串，从start 开始取count个；

 

 

select substr('130888888888',3,8)from dual;
  08888888
select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename))) from emp;

(9)rpad,lpad 粘粘字符
  RPAD 在列的右边粘粘字符
  lpad 在列的左边粘粘字符

 

 

select lpad(rpad('gao',10,'A'),17,'A') from dual;
 *******gao*******
 select lpad('page 1'15,'*.') from dual;
 select rpad(ename,30,'*'),sal from emp;

(10)使用层次查询取得职位最高的雇员及其以下一级雇员的雇员的顾远明和岗位

 

 

select lpad(' ',3*(level-1))||enama ename,lpad(' ',3*(level-1))||job job,from emp
  where level<3 start with mgr is null connect by mgr=prior empno;

(11)ltrim和rtrim

ltrim删除左边出现的字符串 空格  rtrim 删除右边出现的 空格

 

SQL> select Ltrim(Rtrim('   gao qian jing     ',' '),' ') from dual;

(12)replace('string','s1','s2')

 

 

select replace('he love you','he','i')from dual;

(13) soundex 返回一个与给定字符串读音相同的字符串

create table table1(sound varchar(8));
insert into table1 values('weather');//('wether')//('gao')
select sound from table1 where soundex(sound)=soundex('weather');