오라클 DBMS - sql - 단일행 함수 (1)

select initcap(ID) from student where deptno1 = 201;

select initcap('Pretty girl') from dual;

select name 이름, id, lower(ID) 소문자, upper(id) 대문자 from student where deptno1 =201;

select name 이름, id, length(id) 글자수 from student where length(id) > 9;

select name 이름, length(name), lengthb(name) from student where deptno1 = 201;

select concat(name, position) from professor where deptno=101;

select substr('abcde',2,3) from dual;

select substr('abcde',-2,3) from dual;

select name, substr(jumin,1,6) "생년월일" from student where deptno1 = 101;

select name, substr(jumin,1,6) "생년월일" from student where substr(jumin,3,2) = '08';

select substr(name,1,2) from student where deptno1 = 101;

select substrb(name,1,2) from student where deptno1 = 101;

select instr('a*b*c*','*',1,2) from dual;

select instr('a*b*c*','*',3,2) from dual;

select instr('a*b*c*','*',-4,2) from dual;

select name, tel, instr(tel,')',1,1) from student where deptno1 = 101;

select name, tel, substr(tel,1,instr(tel,')',1,1)-1) as "지역번호" from student where deptno1 = 101;

select id, lpad(id,10,'$') "lpad" from student where deptno1 = 101;

select lpad(dname,10,'1234567890') "LPAD 연습" from dept2;

select rpad(id,12,'*') from student where deptno1 = 101;

select rpad(dname,10,'7890') "RPAD 연습" from dept2;

select ltrim(dname, '영') from dept2;

select rtrim(dname,'부') from dept2;

select replace(name, substr(name,1,1),'#') from student where deptno1 = 101;

select replace(name, substr(name,2,1),'#') from student where deptno1 = 101;

select name "이름", replace(jumin, substr(jumin,7,12),'*******') "주민번호"  from student where deptno1 = 101;

select name, tel, replace(tel, substr(tel,5,3),'###') "REPLACE" from student where deptno1=102;

select * from reg_test where regexp_like(text,'[a-z][0-9]');

select * from reg_test where regexp_like(text, '[a-z] [0-9]');

select * from reg_test where regexp_like(text, '[a-z]?[0-9]');

select * from reg_test where regexp_like(text, '[a-z]*[0-9]');

select * from reg_test where regexp_like(text, '[A-Z]{3}');

select * from reg_test where regexp_like(text, '[a-z]{3}');

select * from reg_test where regexp_like(text, '[0-9]{3}');

select * from reg_test where regexp_like(text, '[A-Z][0-9]{3}');

select * from reg_test where regexp_like(text, '[0-9][a-z]{3}');

select * from reg_test where regexp_like(text, '[A-Z]{3}');

select * from reg_test where regexp_like(text, '^[0-9]');

select * from reg_test where regexp_like(text, '^[a-z]|^[0-9]');

select * from reg_test where regexp_like(text, '[a-z]$');

select * from reg_test where regexp_like(text, '^[^0-9]');

select * from reg_test where regexp_like(text, '^[^a-z]');

select * from reg_test where regexp_like(text, 'A|1');

select * from reg_test where regexp_like(text, 'a|1');

select * from reg_test where not regexp_like(text, '[a-z|A-Z]');

select * from reg_test where regexp_like(text, '\?');

col "숫자->기호" for a10

select text, regexp_replace(text,'[[:digit:]]','+') "숫자->기호" from reg_test;

col "패턴추가" for a15

select text, regexp_replace(text,'([0-9])','\1*') "패턴추가" from reg_test;

select regexp_replace('abc 123','( ){1,}','') from dual;

select studno, name, id from student where id = ltrim(regexp_replace('&id','( ){1,}',''),' ');

select studno, name, id from student where id = lower(ltrim(regexp_replace('&id','( ){1,}',''),' '));

set verify off

select studno, name, id from student where id = lower(ltrim(regexp_replace('&id','( )

select regexp_replace('20130324','([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})','\1:\2:\3') from dual;