1.1 字符函数

字符函数接受字符参数，这些字符可以来自于一个表中的列，或者更一般地讲，可以来自于任意表达式。然后字符函数会按照某种方式处理输入参数，并返回一个结果。字符函数的一个例子是UPPER()，其功能是将字符串中的所有字母全部转换成大写，并返回新的字符串。另外一个例子是NVL()，其功能是将一个空值转换成传递给该函数的值。表1-1列出了一些字符函数。在表1-1和随后的所有语法定义中，x和y都可以表示一个表中的列，或者更一般地讲，可以是任意有效的表达式。

表1-1 字符函数

函数	说明
ASCII(x)	返回字符x的ASCII码
CHR(x)	返回ASCII码为x的字符
CONCAT(x, y)	将y添加在x上，并将得到的字符串作为结果返回
INITCAP(x)	将x中每个单词的首字母都转换成大写，并返回得到的字符串
INSTR(x, find_ string [, start] [, ccurrence])	在x中查找字符串find_string，然后返回find_string所在的位置。可以提供一个可选的start位置来指定该函数从这个位置开始查找。同样，也可以指定一个可选的occurrence参数，来说明应该返回find_string第几次出现的位置
LENGTH(x)	返回x中字符的个数
LOWER(x)	将x中的字母转换成小写，并返回得到的字符串
LPAD(x, width[, pad_string])	在x的左边补齐空格，得到总长为width个字符的字符串。该函数可以提供一个可选的pad_string，这个参数用于指定重复使用哪个字符串来补齐x左边的空位。返回x被补齐之后的结果字符串
LTRIM(x[, trim_string])	从x的左边截去一些字符。可以使用一个可选的参数trim_string来指定要截去的字符；如果不指定trim_string参数，则默认截去空格
NANVL(x, value)	如果x匹配NAN这个特殊值(非数字)，就返回value；否则返回x。(这是Oracle Database 10g新增加的函数)
NVL(x, value)	如果x为空，就返回value；否则返回x
NVL2(x,value1, alue2)	如果x非空，就返回value1；否则返回value2
REPLACE(x,search_string, replace_string)	在x中查找search_string，并将其替换为replace_string
RPAD(x,width[,pad_string])	与LPAD()类似，不过要对x的右边补齐
RTRIM(x[,trim_string])	与LTRIM()类似，不过要对x的右边截去
SOUNDEX(x)	返回一个包含x的发音的字符串。该函数用于对英文发音不同但却比较接近的单词进行比较
SUBSTR(x, start[, length])	返回x中的一个子字符串，这个子字符串从start处开始，还可以为这个子字符串指定一个可选的length参数
TRIM({[LEADING \| TRAILING \| BOTH] trim_string FROM} x )	从x的左边和右边同时截去一些字符。可以使用一个可选的参数trim_string来指定要截去的字符；如果不指定trim_string参数，则默认截去空格；LEADING删除字符串左边的字符，TRAILING删除字符串右边的字符， BOTH删除字符串两边的字符
UPPER(x)	将x中的字母转换为大写，并返回得到的字符串

1. ASCII()和CHR()

ASCII(x)函数用于获得字符x的ASCII码，CHR(x)函数用于获得ASCII码为x的字符。

下面这个查询使用ASCII函数获得a、A、0以及9的ASCII码：

SELECT ASCII('a'), ASCII('A'), ASCII('z'), ASCII('Z'), ASCII(0), ASCII(9)

FROM dual;

ASCII('a') ASCII('A') ASCII('A') ASCII('A') ASCII(0) ASCII(9)

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

97 65 122 90 48 57

注意：

在此查询中我们使用了dual表。dual表只包含一行；这个表可以用来执行不对任何特定表进行操作的查询。

下面这个查询使用CHR函数获得ASCII码为97、65、122、90、48及57的字符：

SELECT CHR(97), CHR(65), CHR(122), CHR(90),CHR(48), CHR(57)

FROM dual;

C C C C C C

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

a A z Z 0 9

注意在此查询中，CHR函数返回的字符与前一个例子中传递给ASCII函数的那些参数完全相同。这说明CHR函数和ASCII函数的效果是互逆的。

2. CONCAT()

CONCAT(x, y)函数用于将y添加在x之后，该函数会返回得到的字符串。

下面这个查询使用CONCAT函数将last_name添加在first_name之后：

SELECT CONCAT(first_name, last_name)

FROM customers;

CONCAT(FIRST_NAME,LAST_NAME)

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

JohnBrown

CynthiaGreen

SteveWhite

GailBlack

DoreenBlue

注意：

CONCAT函数与 || 操作符的功能完全相同。

3. INITCAP()

INITCAP(x)函数用于将x中每个单词的首字母转换成大写。

下面这个查询从products表中选择product_id和description列，并使用INITCAP函数将description列中的每个单词的首字母转换成大写：

SELECT product_id, INITCAP(description)

FROM products

WHERE product_id < 4;

PRODUCT_ID INITCAP(DESCRIPTION)

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

1 A Description Of Modern Science

2 Introduction To Chemistry

3 A Star Explodes

4. INSTR()

INSTR(x,find_string[,start][,occurrence])函数用于在x中查找find_string。INSTR()函数返回find_string所在的位置，该函数可以使用可选参数start来说明从x中的哪个位置开始查找，还可以使用可选参数occurrence说明应该返回find_string第几次出现的位置。

下面这个查询从products表中选择product_id为1的行的name列，并显示字符串Science在其中的位置：

SELECT name, INSTR(name, 'Science')

FROM products

WHERE product_id = 1;

NAME INSTR(NAME,'SCIENCE')

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

Modern Science 8

下面这个查询使用INSTR函数显示了从产品名的开头开始第二次出现字母e的位置：

SELECT name, INSTR(name, 'e', 1, 2)

FROM products

WHERE product_id = 1;

NAME INSTR(NAME,'E',1,2)

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

Modern Science 11

注意在字符串Modern Science中第二个e是第11个字符(空格也算一个字符)。

在字符函数中也可以使用日期。下面这个查询使用INSTR函数显示了第一个顾客的dob中字符串JAN的位置。

SELECT customer_id, dob, INSTR(dob, 'JAN')

FROM customers

WHERE customer_id = 1;

CUSTOMER_ID DOB INSTR(DOB,'JAN')

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

1 01-JAN-65 4

5. LENGTH()

LENGTH(x)函数用于获得x中字符的个数。下面这个查询使用LENGTH函数显示了products表中name列中字符串的长度：

SELECT name, LENGTH(name)

FROM products;

NAME LENGTH(NAME)

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

Modern Science 14

Chemistry 9

Supernova 9

Tank War 8

下面这个查询使用LENGTH函数显示了构成产品价格(price列)的字符总数；注意小数点(.)也计算在字符总数之内。

SELECT price, LENGTH(price)

FROM products

WHERE product_id < 3;

PRICE LENGTH(PRICE)

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

19.95 5

30 2

6. LOWER()和UPPER()

LOWER(x)函数用于将x中的字母转换成小写。同理，UPPER(x)函数用于将x中的字母转换成大写。

下面这个查询从customers表中选择first_name和last_name列，使用UPPER函数将first_name列转换成大写，并使用LOWER函数将last_name列中的字符串转换成小写：

SELECT UPPER(first_name), LOWER(last_name)

FROM customers;

UPPER(FIRST_NAME) LOWER(LAST_NAME)

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

JOHN brown

CYNTHIA green

STEVE white

GAIL black

7. LPAD()和RPAD()

LPAD(x, width [, pad_string]) 函数用于在x的左边补齐空格，使x的总长度达到width个字符。如果在pad_sring参数中指定了一个字符串，那么就使用这个字符串来重复填充x左边的空位，以补齐x的长度。补齐后的字符串作为结果返回。同理，RPAD(x, width [, pad_string]) 函数用于在x的右边补齐字符串。

下面这个例子从products表中选择name和price列。name列使用RPAD函数进行右补齐，长度达到30个字符，并且使用小数点来补齐空位。price列使用LPAD函数进行左补齐，长度达到8个字符，并且使用 *+ 字符来补齐空位。

SELECT RPAD(name, 30, '.'), LPAD(price, 8, '*+')

FROM products

WHERE product_id < 4;

RPAD(NAME,30,'.') LPAD(price, 8, '*+')

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

Modern Science................ *+*19.95

Chemistry..................... *+*+*+30

Supernova..................... *+*25.99

注意：

这个例子说明可以对数字应用一些字符函数。具体来说，就是这个例子中的price列包含了数字，而我们使用LPAD函数进行了左补齐。

8. LTRIM()、RTRIM()和TRIM()

LTRIM(x [,trim_string])函数用于从x的左边截去一些字符，该函数还可以使用可选的参数trim_string来指定要截去的字符；如果没有指定trim_string参数，默认情况下会截去空格。同理，RTRIM函数用于从x的右边截去一些字符，TRIM函数用于从x的左边和右边各截去一些字符。下面这两个查询用到了这3个函数：

SELECT LTRIM(' Hello Gail Seymour!'),RTRIM('Hi Doreen Oakley!abcabc', 'abc')

FROM dual;

LTRIM(' Hello Gail Seymour!') RTRIM('Hi Doreen Oakley!abcabc', 'abc')

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

Hello Gail Seymour! Hi Doreen Oakley!

SELECT TRIM(’0’FROM '0Hey0'),TRIM(LEADING '0' FROM '0Hey0'),TRIM(TRAILING '0' FROM '0Hey0')

FROM dual;

TRIM(’O’ TRIM(TRAILING '0' TRIM(TRAILING '0'

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

Hey Hey0 0Hey

9. NVL()

NVL函数用于将空值转换成一个已知的值。NVL(x,value)的结果如下：如果x为空，则返回value；否则返回x。

下面这个查询从customers表中选择customer_id和phone列。如果phone列的值为空，则使用NVL函数将其转换为字符串Unknown Phone Number。

SELECT customer_id, NVL(phone, 'Unknown Phone Number')

FROM customers;

CUSTOMER_ID NVL(phone, 'Unknown Phone Number')

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

1 800-555-1211

2 800-555-1212

3 800-555-1213

4 800-555-1214

5 Unknown Phone Number

顾客#5的phone列被转换为Unknown Phone Number，因为此行的phone列值为空。

10. NVL2()

NVL2(x, value1, value2)的返回结果如下：如果x非空，则返回value1；否则返回value2。

下面这个查询从customers表中选择customer_id和phone列。如果phone列的值非空，则使用NVL2函数将其转换为字符串Known；如果如果phone列的值为空，则使用NVL2函数将其转换为Unknown：

SELECT customer_id, NVL2(phone, 'Known', 'Unknown')

FROM customers;

CUSTOMER_ID NVL2(phone, 'Known', 'Unknown')

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

1 Known

2 Known

3 Known

4 Known

5 Unknown

注意顾客#1到#4的phone列的值被转换成了Known，因为这些记录的phone列都不为空。而顾客#5的phone列的值则被转换成了Unknown，因为该行的phone列的值为空。

11. REPLACE()

REPLACE(x, search_string, replace_string)用于在x中查找search_string，并将其替换为replace_string。

下面这个例子使用REPLACE函数从products表中查询product_id为1的记录的name列(其name列为Modern Science)，并将字符串Science替换为Physics：

SELECT REPLACE(name, 'Science', 'Physics')

FROM products

WHERE product_id = 1;

REPLACE(NAME,'SCIENCE','PHYSICS')

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

Modern Physics

注意：

REPLACE函数并不会真正对数据库中的数据进行修改，而是只对结果集中返回的行进行修改。

12. SOUNDEX()

SOUNDEX(x)用于获得包含x发音的一个字符串，该函数用于对英文拼写不同但发音相似的单词进行比较。

下面这个查询使用SOUNDEX函数从customers表中选择last_name发音类似于whyte的行的last_name列：

SELECT last_name

FROM customers

WHERE SOUNDEX(last_name) = SOUNDEX('whyte');

LAST_NAME

----------

White

下面这个查询得到发音类似于“bloo”的last_name列：

SELECT last_name

FROM customers

WHERE SOUNDEX(last_name) = SOUNDEX('bloo');

LAST_NAME

----------

Blue

13. SUBSTR()

SUBSTR(x, start[, length])用于从x中取得从start位置开始的一个子字符串，还可以使用可选参数length指定子字符串的长度。

下面这个查询使用SUBSTR函数选择products表中name列的一个子字符串：它从name列的第二个字符开始，长度为7个字符：

SELECT SUBSTR(name, 2, 7)

FROM products

WHERE product_id < 4;

SUBSTR(name, 2, 7)

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

odern S

hemistr

upernov

14. 在函数中使用表达式

在函数中并非只能使用表中的列：也可以提供任意有效的表达式，条件是这个表达式的结果是一个字符串。下面这个查询使用SUBSTR函数从字符串Mary had a little lamb中选择子字符串little：

SELECT SUBSTR('Mary had a little lamb', 12, 6)

FROM dual;

SUBSTR('Mary had a little lamb', 12, 6)

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

little

15. 函数组合

在一个SQL语句中，可以使用函数的任意有效组合。下面这个查询就使用了一个函数组合，其中包括UPPER函数和SUBSTR函数；注意SUBSTR()函数的输出被作为参数传递给UPPER函数：

SELECT name, UPPER(SUBSTR(name, 2, 8))

FROM products

WHERE product_id < 4;

NAME UPPER(SUBSTR(name, 2, 8))

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

Modern Science ODERN SC

Chemistry HEMISTRY

Supernova UPERNOVA

注意：

这种函数组合的能力不只限于字符函数。任何有效的函数组合都可以正常使用。

ITPub博客

Oracle SQL函数(二) Hacker