【数据库】Oracle中的字符型及处理方法

与其他编程语言一样，oracle中同样存在着数据类型。

oracle中的数据类型主要有两个应用场景：一是用于指定数据表中列的类型；二是用于PL/SQL编程中声明变量。

oracle的数据类型主要包括：字符型（character）、数值型（number）、日期型（date）和大对象型（LOB）。

同时，oracle提供了针对数据类型的内置函数。本文着重讲述oracle中的字符型及其相关的函数，包括：字符型简介、字符型函数。

1、字符型简介

oracle中的字符型有3种：分别是char(n)、varchar(n)、varchar2(n)。

1.1 固定长度字符串-----char(n)

char(n)指定变量或列的数据类型为固定或列的数据类型为固定长度的字符串。其中，n代表字符串的长度。当实际字符串的长度不足n时，oracle利用空格在右端补齐。当然，oracle不允许实际字符串的长度大于n.

数据库中列指定为char(n)类型时，n的最大值不能大于2000，否则，oracle将抛出错误。

1.2 varchar(n)

oracle中提供了varchar(n)的数据类型。该类型是oracle迎合工业标准中的varchar而制定的。该数据类型实际是一个可变长度字符串类型，也就是说：当实际字符串的长度不足时，不会使用空格进行填充。同样，实际字符串的长度也不允许超出n。

例如：当作为列的数据类型出现时，varchar的最大长度不能大于4000，如下：

alter table test_char add f_varchar varchar(4001);

会报错：ORA-00910：specified length too long for its datatype

alter table test_char add f_varchar varchar(4001)用于为表test_char添加类型为varchar(4000)的列，报错表明：指定长度已经大于数据类型varchar(n)的最大长度。

1.3 varchar2(n)

与varchar(n)类型，varchar2(n)同样是可变长度的字符串类型，oracle在工业标准之外，自定义了该数据类型。同时oracle也提醒用户，尽量使用varchar2(n)，而非varchar(n)。因为使用varchar2(n)可以获得oracle向后兼容性的保证。

【注意】varchar2和varchar一样，最大长度不能大于4000

2、字符型分析

对于一般用途的数据表来说，最常用的字符串类型为varchar2(n)。下面将着重分析char(n)与varchar2(n)的区别

2.1 varchar2(n)与char(n)的区别

varchar2(n)为可变字符串类型，而char(n)为固定字符串类型。二者的区别在于是否使用空格来补齐不足的部分。

列子：在表test_char中，列f_char和f_varchar2的长度分别为2000和4000，先向其中插入新的记录，并为两列赋予相同的值。

SQL>insert into test_char(f_char, f_varchar2)

valuse('000','000');

上面是均为列f_char和f_varchar2赋值“000”，长度为3.通过数据库查询工具查询时，用户无法察觉到二者的区别。

SQL>select * from test_char;

f_char    f_varchar    f_varchar2

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

00                                  000

但是，通过length()函数来查看此时两列的实际长度。

SQL>select length(f_char), length(f_varchar2) from test_char;

此时查出来，可以看出，f_char 为固定字符串类型，因此长度为2000，而f_varchar2为可变字符型，长度为3.这代表f_char已经占用了2000字节的空间，而f_varchar2则占用了3个字节的空间。

2.2 varchar2(n)与char(n)的选择

通过上面例子，可以看出，char(n)类型的列通常占用较大的存储空间；而varchar2(n)类型的列占用的空间较小。所以，varchar2(n)类型是在进行数据库设计时的一般选择，但这，并不意味着char(n)类型应该被摒弃。相反，char(n)在效率方面要高于varchar2(n)。这是因为可变长度的字符串类型在实际数据长度发生改变时，总需要不断调整存储空间，尤其是频繁修改数据，而数据长度也不断改变的情况下，这种效率的损耗尤其明显。

大多数的应用程序，并不将数据库端的效率作为首要考虑的需求，而更倾向于较小的空间代价，因此一般使用varchar2(n)来定义列。而char(n)则是典型的“以空间换时间”的例子。所以，在实际开发中，酌情选择。

2.3 变量声明中的字符串类型

3种字符串类型----char(n)、varcha(n)、varchar2(n)，都可用于声明变量。但是，利用三者声明时，最大长度均为32767.

3. 字符型处理

oracle提供了丰富的字符串函数来处理字符型数据。

3.1 向左不全字符串----lpad()函数

lpad()函数用于向左补全字符串，该函数主要用于字符串的格式化，格式化的方式为，将字符串格式化为指定长度，如有不足部分，则在字符串的左端填充特定字符，其调用方式如下所示。

lpad(string, paded_length, [pad_string])

其中，第一个参数指定原始字符串；第二个参数指定格式化之后的字符串长度；第三个参数指定使用哪个字符填充不足位数。

例子：员工工号希望被格式化为4位数字，不足部分使用“0”进行填充。例如：工号1，应被格式化为“0001”，那么此时可以利用lpad()函数。

SQL>select lapd('1',4 ,'0') employee_no from dual;

其中，lapd('1',4 ,'0')用于格式化字符串“1”为长度为4的字符串，并利用“0”自左端起，将不足部分补全。

当然，如果原字符长度本身已超过预期长度，也将从字符串左端进行截取。

SQL>select lpad('12345', 4 ,'0') employee_no from dual;

1234

lpad('12345', 4 ,'0') 试图返回依据字符串“12345”格式化后的4位字符，并使用“0”自左端填充不足位数，但是“12345”已经超出预期长度4，oracle将返回自左端截取的4位字符。

3.2 向右补全字符串----rpad()函数

与lpad()函数相似，rpad()函数返回字符串格式化为特定位数的操作。只是该函数自右端补全不足位数。

例子：利用rpad()函数实现右端补全字符串。

SQL>select rapd('1',4,  '*') employee_no from dual;

1***

 rapd('1',4,  '*') 用于右端补全字符串“1”，预期长度为4，并使用“*”补全不足位数。需要注意的是，当原始字符串长度大于预期长度时，rplad()函数同样是自左端截取字符串，如下：

SQL>select  rapd('12345',4,  '*')  employee_no from dual;

1234

 rapd('12345',4,  '*')  中，原字符串为“12345”，预期长度为4，格式化结果“1234”是自原始字符串左端截取4位所得到的字符串。

3.3 返回字符串的小写形式-----lower()函数

lower()函数用于返回字符串的小写形式。该函数仅有一个参数，即原始字符串。

例子：在数据库中，当查询条件可以忽略大小形式时，通常利用lower()函数进行字符串大小写形式的统一。

SQL> select username, password from dba_users where lower(username) = 'system';

where lower(username) = 'system'指定查询条件，并利用lower(username)将列username 转换为小写形式，以便在比较时可以忽略大小形式。

3.4 返回字符串的大写形式----upper()函数

与lower()函数相反，upper()函数用于返回字符串的大写形式，该函数仅有一个参数，即原始字符串。

例子：同样可以用upper()函数改写上面例子查询语句。

SQL>select username, password from dba_users where upper(username) = 'SYSTEM';

where upper(username) = 'system'指定查询条件，并利用upper(username)将列username 转换为大写形式，以便在比较时可以忽略大小形式。

3.5 单词首字符大写----initacap()函数

inticap()函数用于将单词转换为首字符大写、其他字符小写的形式。

例子：利用initcap()函数格式化单词。

SQL>select initcap('like') new_word from dual;

-----

Like

SQL>select initcap('LIKE') new_word from dual;

------

Like

分析查询结果可知，无论使用全小写形式的“like”，还是全大写形式的“LIKE”，经过initcap()函数转换后，都将返回首字符大写、其余字符小写的形式“Like”。

利用initcap()函数转换的是以单词为单位，而非整个字符串。例如：某个字符串含有多个单词，那么单个单词都将执行相同的操作。

SQL>select initcap('we all like bike') new_string from dual;

------

We All Like Bike

initcap('we all like bike')中的各个单词----we、all、like、bike都将执行initcap()函数的格式化操作，这里需要注意的是，只要是非单词字符都将作为单词的分隔符。

例如：利用“-”进行分隔的字符串将被视作多个单词。

SQL>select initcap('we-all-like-bike') new_string from dual;

------

We-All-Like-Bike

3.6 返回字符串长度---length()函数

length()函数可用于返回字符串长度。

例如：利用length()函数返回字符长度。

SQL>select length('12345') len from dual;

对于length()函数来说，无论是单字节字符还是双字节字符，都将被视作一个字符进行计算。

SQL>select length('中国人') len from dual;

-----

3

分析查询结果，对于length()函数来说，“中国人”中的三个双字节字符，均被计算为1.

length()函数的参数不仅可以为字符串，还可以为其他数据类型，如数值型。

SQL>select length(123.45) len from dual;

-----

6

length(123.45)用于返回数字123.45的长度，oracle首先将其转换为字符串“123.45”，然后获得字符串的长度为6.

3.7 截取字符串----substr()函数

substr()函数用于截取字符串，该函数的一般调用形式如下所示。

substr(string, start_index, length)

其中，第一个函数string 指定原始字符串；第二个参数start_index指定开始截取的位置；第三个参数指定截取的长度。

需要注意的是，oracle中字符串中第一个字符的位置为1.

这与很多编程语言，如Java中自0开始的习惯不同。

例子：利用substr()函数截取字符串。

SQL>select substr('123456789', 2, 3) sub_string from dual;

----

234

substr('123456789', 2, 3) 用于自“123456789”的第二位开始，截取长度为3的子字符串。返回结果为“234”。