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

SQL Fundamentals || Oracle SQL语言

 

SQL Fundamentals: Using Single-Row Functions to Customize Output使用单行函数自定义输出

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

SQL Fundamentals || Single-Row Functions || 数字函数number functions

SQL Fundamentals || Single-Row Functions || 日期函数date functions

SQL Fundamentals || Single-Row Functions || 转换函数 Conversion function

SQL Fundamentals || Single-Row Functions || 通用函数 General function

      字符函数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()函数有两种形式：

从指定位置截取到结尾	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()函数功能相同.

Character Functions

Single-row character functions accept character data as input and can return both character and numeric values. Character functions can be divided into the following:

       

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搜索字符串，替换

function	result
CONCAT('Hello', 'World')	HelloWorld
SUBSTR('HelloWorld', 1[,5]) (从第一个字符开始取，取5个字符)	Hello
LENGTH('HelloWorld)	10
INTER('HelloWorld','w')	6
LPAD(salary, 10, '*')   RPAD(salary, 10, '*')	*****24000 24000***** 注：这里24000是salary的值 SQL> select rpad(sal,10,'-') from scott.emp;   RPAD(SAL,10,'-') ---------------------------------------- 800------- 1250------
TRIM('H' FROM 'HELLOWORD')	ELLOWORD SQL> select trim('w' from 'word') from dual;   TRI --- ord
REPLACE('JACK AND JUE', 'J', 'BL')	BLACK AND BLUE

综合应用：

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

 

 

function	purpose
LOWER(column | expression)	Coverts alpha character values to lowercase 将字符串转换为小写字母 Coverts mixed-case or uppercase character strings to lowercase
UPPER(column | expression)	Coverts alpha character values to uppercase 将字符串转换为大写字母 Converts mixed-case or lowercase character strings to uppercase
INITCAP(column | expression)	Coverts alpha character values to uppercase for the first letter of each word; all other letters in lowercase 将字符串中每个单词首字母大写，其他小写 Converts the first letter of each word to uppercase and the remaining letters to lowercase
CONCAT(column | expression)	Concatenates the first character value to the second character value; equivalent to concatenation operator(||) 把两个字符串连接起来
SUBSTR(column | expression, m[,n])	Returns specified characters from character value starting at character position m, n characters long(if m is negative, the count starts from the end of the character value, if n is omitted, all characters to the end of the string are returned ) 从字符串中返回指定字符，从字符m开始，n个字符长（如果m是负数，则计数从字符值的结尾开始，如果省略n，则返回字符串结尾的所有字符）
LENGTH(column | expression)	Returns the number of characters in the expression
INTER(column | expression, 'string', [,m],[n]) 查找子字符串在表达式中的为位置 m为开始搜索的位置，n表示字符串第几次出现.	Returns the numeric position of a named string. Optionally, you can provide a position m to start searching ,m 表示开始搜索的位置，and the occurrence n of the string. n表示字符串第几次出现. m and n default to 1, m,n默认都是1，meaning start the search at the beginning of the string and report the first occurrence.
LPAD(column | expression, n, 'string')   RPAD(column | expression, n, 'string') 用一个给定的字符string来填出这个表达式/字符串，填充完以后总长度为n.	Returns an expression left-padded(左填充) to legth of n characters with a character expression. Returns an expression right-padded to length of n characters with a character expression. 例子：LPAD(column | expression, 5, 'w') 给这个字符串左边填充w，直到填充后的字符串总长度为5
TRIM(leading|trailing|both, trim_character FROM trim_source)	Enables you to trim(削减) leading（前导） or trailing(尾部) characters(or both) from a character string. If trim_character on trim_source is a character literal, you must enclose it in single quotation marks. leading是前面的空格 trailing是后面的空格 both是两端的空格 去掉字符串的首尾空格，或者也可以使用trim_source去掉特定字符串
REPLACE(text, search_string, replacement_string) 在文本里搜指定字符串，将搜寻到的字符串替换为替换字符串.	Searches a text expression for a character string and, if found, replaces it with a specified replacement string