SQL Fundamentals || Single-Row Functions || 字符函数 character functions

函数名称

描述

UPPER( 列 | 字符串)

将字符串的内容全部转大写

SQL> SELECT UPPER('wendy') FROM dual;

LOWER( 列 | 字符串)

将字符串的内容全部转小写

SQL> SELECT LOWER('WENDY') FROM dual;

INITCAP( 列 | 字符串)

将字符串的开头首字母大写

SQL> SELECT INITCAP('WENDY') FROM dual;

SQL> SELECT ename ,INITCAP(ename) FROM emp;

REPLACE(列 | 字符串, 新的字符串)

使用新的字符串替换旧的字符串

SELECT ename , REPLACE(ename,'A','_') FROM emp ;

LENGTH(列 | 字符串)

求出字符串长度

SQL> SELECT * FROM emp WHERE LENGTH(ename)=5;

SUBSTR(列 | 字符串, 开始点 [, 长度])

字符串截取

SUBSTR()函数有两种形式：

（1）查询姓名先三位为JAM的

SQL> SELECT * FROM emp WHERE SUBSTR(ename,0,3)='JAM';

（2）查询某部门姓名前三位.

SELECT ename , SUBSTR(ename,3)  FROM emp WHERE deptno=10 ;

（3）查询姓名后三位

SELECT ename,SUBSTR(ename,LENGTH(ename)-2)  FROM emp ;

SELECT ename,SUBSTR(ename,-3)  FROM emp ;

在oracle数据库中，下标都是从1开始，如果设置为0，也会自动将其转换为1.

java语言中字符串下表是从0开始，并且java语言中的substring的方法不能设置负数.

ASCII(字符)

返回与指定字符对应的十进制数字

SQL> SELECT ASCII('A') FROM dual;

CHR(数字)

给出一个整数，并返回与之对应的字符

SQL> SELECT CHR(100) FROM DUAL;

RPAD(列 | 字符串 , 长度 , 填充字符)

LPAD(列 | 字符串 , 长度 , 填充字符)

在右或左填充指定长度字符串

SELECT LPAD('MLDN' , 10 , '*') LPAD函数使用 , RPAD('MLDN' , 10 , '*') RPAD函数使用 ,

LPAD(RPAD('MLDN' , 10 , '*') , 16 , '*') 组合使用

FROM dual ;

LTRIM(字符串)、RTRIM（字符串）

去掉左或右空格

SELECT '   MLDN LiXingHua     ' , LTRIM('   MLDN LiXingHua     ')  FROM dual ;

SELECT '   MLDN LiXingHua     ' , RTRIM('   MLDN LiXingHua     ')   FROM dual ;

TRIM(列 | 字符串)

去掉左右空格

SELECT '   MLDN LiXingHua     ' , TRIM('   MLDN LiXingHua     ') FROM dual ;

不能去掉中间空格.

INSTR(列 | 字符串, 要查找的字符串 , 开始位置 , 出现位置)

查找一个子字符串是否在指定的位置上出现

SELECT INSTR('MLDN Java' , 'MLDN') 查找得到 ,

INSTR('MLDN Java' , 'Java') 查找得到 ,

INSTR('MLDN Java' , 'JAVA') 查找不到

FROM dual ;

如果能找到就返回位置，如果查不到就返回0

这个函数和JAVA中的indexof()函数功能相同.

Case-conversion functions

大小写转换函数

• Case Conversion functions - Accepts character input and returns a character value. Functions under the category are UPPER, LOWER and INITCAP.
• UPPER function converts a string to upper case.
• LOWER function converts a string to lower case.
• INITCAP function converts only the initial alphabets of a string to upper case.

Lower转换为小写

Upper转换为大写

initcap首字母大写，其他小写

SQL> select lower('SQL Function') from dual;

LOWER('SQLFU

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

sql function

SQL> select upper('SQL Function')  from dual;

UPPER('SQLFU

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

SQL FUNCTION

SQL> select initcap('sql function')  from dual;

INITCAP('SQL

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

Sql Function

SQL> select 'The job id for '||UPPER(ename)||' is '||LOWER(JOB) AS "emp details" FROM scott.emp;

emp details

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

The job id for SMITH is clerk

The job id for WARD is salesman

应用：有时候不知道查询的名字是大写还是小写，在匹配的时候可能找不到，就使用LOWER将名字全部转换为小写，再来匹配.

SQL> select ename,job FROM emp WHERE ename='higgins';

SQL> select ename,job FROM emp WHERE LOWER(ename)='higgins';

Case Conversion functions

The SELECT query below demonstrates the use of case conversion functions.

SELECT UPPER (first_name), INITCAP (last_name), LOWER (job_id)
FROM employees
WHERE rownum < 5;

UPPER(FIRST_NAME)    INITCAP(LAST_NAME)        LOWER(JOB_
-------------------- ------------------------- ----------
STEVEN               King                      ad_pres
NEENA                Kochhar                   ad_vp
LEX                  De Haan                   ad_vp
ALEXANDER            Hunold                    it_prog

Character-manipulation functions

字符操作函数

• Character functions - Accepts character input and returns number or character value. Functions under the category are CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM and REPLACE.
• CONCAT function concatenates two string values.
• LENGTH function returns the length of the input string.
• SUBSTR function returns a portion of a string from a given start point to an end point.
• INSTR function returns numeric position of a character or a string in a given string.
• LPAD and RPAD functions pad the given string upto a specific length with a given character.
• TRIM function trims the string input from the start or end.
• REPLACE function replaces characters from the input string with a given character.

CONCAT连接||操作

SUBSTR取子字符串

LENGTH求字符串的长度

INTER返回的是一个数字，查询一个子字符串在字符串中的第几个位置

LPAD左填充

RPAD右填充

TRIM去掉字符串的首尾空格或特殊字符（注意只能去掉首尾空格或字符）

REPLACE搜索字符串，替换

综合应用：

SELECT empid, CONCAT(first_name,last_name) NAME, jobid, LENGTH(last_name), INSTR(last_name,'a') "Contains 'a'?"

FROM emp

WHERE substri(jobid,4)='REP';

Character functions

The SELECT query below demonstrates the use of CONCAT function to concatenate two string values.

SELECT CONCAT (first_name, last_name)
FROM employees
WHERE rownum < 5;

CONCAT(FIRST_NAME,LAST_NAME)
--------------------------------
EllenAbel
SundarAnde
MozheAtkinson
DavidAustin

The SELECT query below demonstrates the use of SUBSTR and INSTR functions. SUBSTR function returns the portion of input string from 1st position to 5th position. INSTR function returns the numeric position of character 'a' in the first name.

SELECT SUBSTR (first_name,1,5), INSTR (first_name,'a')
FROM employees
WHERE rownum < 5;

SUBST INSTR(FIRST_NAME,'A')
----- ---------------------
Ellen                     0
Sunda                     5
Mozhe                     0
David                     2

The SELECT query below demonstrates the usage of LPAD and RPAD to pretty print the employee and job information.

SELECT RPAD(first_name,10,'_')||LPAD (job_id,15,'_')
FROM employees
WHERE rownum < 5;

RPAD(FIRST_NAME,10,'_')||
-------------------------
Steven____________AD_PRES
Neena_______________AD_VP
Lex_________________AD_VP
Alexander_________IT_PROG