字符类型函数

发布时间:2014-10-23 23:25:24
来源:分享查询网

字符型 ASCII CHR chr(39) chr(10) chr(9) LOWER UPPER INITCAP  CONCAT || SUBSTR LENGTH INSTR TRIM dump lpad rpad REPLACE translate B19306 books  sql 第5章 functions ASCII('字符') 返回字符的ASCII码值 idle> select ASCII('a') from dual; ASCII('A') ---------- 97 idle> select ASCII('A') from dual; ASCII('A') ---------- 65 idle>  CHR('n') 返回n的字符值 n是ASCII码数 idle> select chr(65) from dual; C - A idle> select chr(39) from dual; C - ' idle>  但是求单引号的ASCII码写法很特殊 两个单引带表一个单引 idle> select ASCII(''') from dual; ERROR: ORA-01756: quoted string not properly terminated idle> select ASCII('''') from dual; ASCII('''') -----------  39 idle>  LOWER(列名|表达式) 小写转换 idle> select lower('ABC') from dual; LOW --- abc idle>  dual 为虚表,当一个语句不需要从表中获取数据时,但又要维持SQL语法,oracle才提供了虚表来解决这个问题 UPPER(列名|表达式) 大写转换 idle> select upper('abc') from dual; UPP --- ABC idle>  INITCAP(列名|表达式) 每个词的词头大写 其他小写 idle> select initcap('abC dEf XYZ') from dual; INITCAP('AB ----------- Abc Def Xyz idle>  CONCAT(列名|表达式,列名|表达式) 将第一个字符串和第二个字符串连接 idle> select concat('abc','xyz') from dual; CONCAT ------ abcxyz 不太常用,因为我们可以用 || 连接 idle> select 'abc'||'xyz' from dual; 'ABC'| ------ abcxyz idle>  SUBSTR(列名|表达式,m,[n]) 返回指定子串,该子串是从第m个字符开始,其长度为n,不指定n值则从m到最后 idle> select substr('abcdefxyz',4,3) from dual; SUB --- def idle> select substr('abcdefxyz',4) from dual; SUBSTR ------ defxyz idle>  LENGTH(列名|表达式) 返回字符串的长度 idle> select length('abcdefxyz') from dual; LENGTH('ABCDEFXYZ') -------------------   9 idle>  INSTR (列名|表达式,'字符串',[m],[n]) 从表达式或列中搜索给定的字符串的所处位置,m代表从第几个开始搜,n代表第几次出现. m和n默认都是1 idle> select INSTR('abcdddxyz','d') from dual; INSTR('ABCDDDXYZ','D') ----------------------      4 idle>  idle> select INSTR('abcdddxyz','d',5) from dual; INSTR('ABCDDDXYZ','D',5) ------------------------        5 idle> select INSTR('abcdddxyz','d',5,2) from dual; INSTR('ABCDDDXYZ','D',5,2) --------------------------  6 idle>  TRIM([leading|]trailing|both 要去掉的字符 FROM 源字符串) 从源字符串中去掉指定的字符 可以用leading tailing来修饰去掉的字符串是在开头或结尾,默认是两者都  默认截取的是空格 idle> select trim('a' from 'aaabcdeaaafxyzaaa') from dual; TRIM('A'FRO ----------- bcdeaaafxyz idle> select trim(leading 'a' from 'aaabcdeaaafxyzaaa') from dual; TRIM(LEADING'A -------------- bcdeaaafxyzaaa idle> select trim(trailing 'a' from 'aaabcdeaaafxyzaaa') from dual; TRIM(TRAILING' -------------- aaabcdeaaafxyz idle>  左补齐lpad 右补齐rpad 将不足20个字符的位置用指定符号填充. idle> select lpad(ename,20,'-') ,rpad(ename,20,'-') from emp where ename like 'S%'; LPAD(ENAME,20,'-')   RPAD(ENAME,20,'-') -------------------- -------------------- ---------------SMITH SMITH--------------- ---------------SCOTT SCOTT--------------- idle>  REPLACE(源字符串,OLD字符串,NEW字符串) 从源字符串中找到搜索的old字符串,替换成new字符串 idle> select replace('abcdefxyz','def','DEF') from dual; REPLACE(' --------- abcDEFxyz idle>  dump('str'[,FMT[,S,E]]) 用于转换进制格式 str 被转换的字符串 FMT 格式 S   str的开始字符位置 E   S开始的后续结束字符位置 SQL> select dump('abcd') from dual; DUMP('ABCD') -------------------------- Typ=96 Len=4: 97,98,99,100 SQL> SQL> select dump('abcd',1002) from dual; DUMP('ABCD',1002) ------------------------------------------------ Typ=96 Len=4 CharacterSet=AL32UTF8: 97,98,99,100 SQL> SQL> select dump('abcd',1008) from dual; DUMP('ABCD',1008) --------------------------------------------------- Typ=96 Len=4 CharacterSet=AL32UTF8: 141,142,143,144 SQL> DUMP('ABCD',1016) ----------------------------------------------- Typ=96 Len=4 CharacterSet=AL32UTF8: 61,62,63,64 SQL> select dump('abcd',1017) from dual; DUMP('ABCD',1017) ------------------------------------------- Typ=96 Len=4 CharacterSet=AL32UTF8: a,b,c,d SQL>  SQL> select dump('abcd',1017,2,2) from dual; DUMP('ABCD',1017,2,2) --------------------------------------- Typ=96 Len=4 CharacterSet=AL32UTF8: b,c SQL>  SQL> select dump('飞') from dual; DUMP('飞') ------------------------- Typ=96 Len=3: 233,163,158 SQL> select 233*power(256,2)+163*256+158 from dual; 233*POWER(256,2)+163*256+158 ----------------------------     15311774 SQL> select chr(15311774) from dual; CHR --- 飞 SQL>  单行函数的例子 idle> select concat(ename,job) "EMP",substr(job,1,5) title,length(ename),instr(ename,'T',1,2)   from emp  where lower(ename) = 'scott'; EMP     TITLE LENGTH(ENAME) INSTR(ENAME,'T',1,2) ------------------- ----- ------------- -------------------- SCOTTANALYST     ANALY       5     5 idle>

返回顶部
查看电脑版