ITPub博客

首页 > 数据库 > Oracle > Oracle   SQL函数(二) Hacker

Oracle   SQL函数(二) Hacker

Oracle 作者:jianqilive 时间:2013-11-20 16:43:40 0 删除 编辑

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的左边和右边同时截去一些字符。可以使用一个可选的参数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

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

          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)

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

        Description Of Modern Science

        Introduction To Chemistry

        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')

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

         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')

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

    800-555-1211

    800-555-1212

    800-555-1213

    800-555-1214

    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')

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

    Known

    Known

    Known

    Known

    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 little lamb中选择子字符串little:

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

FROM dual;

SUBSTR('Mary had 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博客 ” ,链接:http://blog.itpub.net/23969284/viewspace-1115563/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-05-24