티스토리 뷰

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;





댓글
댓글쓰기 폼