oracle数据库中的通配符,ORACLE学习笔记系列（9）通配符和特殊字符

ORACLE学习笔记系列(9)通配符和特殊字符

一、字符匹配：[NOT] LIKE ‘’

1、可以是一个完整的字符串，也可以含有通配符的字符串。

2、能与LIKE一起使用的通配符包括“%”、“_”。

%(百分号)：表示任意数量的字符，或者可能根本没有字符。

_(下划线)：表示确切的任意单个未知字符。

3、使用LIKE时，NULL不能被%匹配到。

SELECT COUNT(*) AS C FROM DUAL WHERE 'abcde' LIKE '%';

SELECT COUNT(*) AS C FROM DUAL WHERE 'abcde' LIKE 'a%';

SELECT COUNT(*) AS C FROM DUAL WHERE 'abcde' LIKE '%e';

SELECT COUNT(*) AS C FROM DUAL WHERE 'abcde' LIKE '%c%';

SELECT COUNT(*) AS C FROM DUAL WHERE 'abcde' LIKE '_bcde';

SELECT COUNT(*) AS C FROM DUAL WHERE 'abcde' LIKE 'abc__';

SELECT COUNT(*) AS C FROM DUAL WHERE 'abcde' LIKE 'ab_de';

SELECT COUNT(*) AS C FROM DUAL WHERE NULL LIKE '%';

SELECT COUNT(*) AS C FROM DUAL WHERE NULL LIKE '_';

二、常用符号：

SELECT ASCII(' '), --CHR(32)

ASCII('%'), --CHR(37)

ASCII('&'), --CHR(38)

ASCII('*'), --CHR(42)

ASCII('_'), --CHR(95)

ASCII(''''), --CHR(39)

ASCII('"'), --CHR(34)

ASCII(','), --CHR(44)

ASCII('$'), --CHR(36)

ASCII('?'), --CHR(63)

ASCII('^'), --CHR(94)

ASCII('a'), --CHR(97)

ASCII('z'), --CHR(122)

ASCII('0'), --CHR(48)

ASCII('9'), --CHR(57)

ASCII('+'), --CHR(43)

ASCII('-'), --CHR(45)

ASCII('*'), --CHR(42)

ASCII('/'), --CHR(47)

ASCII('='), --CHR(61)

ASCII('|'), --CHR(124)

CHR(10), --是一个换行符\n

CHR(13), --是一个回车符\r

'a' || CHR(10) || 'b',

'a' || CHR(13) || 'b'

FROM DUAL;

三、如何保存特殊字符到数据库中：

--如何插入百分号

INSERT INTO C VALUES (1, '100%'); --可以直接正常保存

INSERT INTO C VALUES (2, '100' || CHR(37) || 200); -- chr(37)代表字符%

--如何插入下划线

INSERT INTO C VALUES (3, 'a_b'); --可以直接正常保存

INSERT INTO C VALUES (4, 'a' || CHR(95) || 'b'); -- chr(95)代表字符_

--如何插入单引号

INSERT INTO C VALUES (5, 'i' || CHR(39) || 'm'); -- chr(39)代表字符'

INSERT INTO C VALUES (6, 'i''m'); -- 两个''可以表示一个'

--如何插入&符号

INSERT INTO C VALUES (7, CHR(38) || '_hello');-- chr(38)代表字符&

--另一种方法，使用 set define off 关闭替代变量的功能，再插入数据。

set define OFF;

INSERT INTO C VALUES (8, '&_hello');

四、如何匹配查询特殊字符，如通配符%与_

--百分号%

SELECT * FROM C WHERE NAME LIKE '100\%' ESCAPE '\';

SELECT * FROM C WHERE NAME LIKE '100\%%' ESCAPE '\';

SELECT * FROM C WHERE NAME LIKE '100' || CHR(37) || '%';

--下划线_

SELECT * FROM C WHERE NAME LIKE 'a\_%' ESCAPE '\';

SELECT * FROM C WHERE NAME LIKE '_\__' ESCAPE '\';

SELECT * FROM C WHERE NAME LIKE '_'||chr(95)||'_' ;--不准确，会查出NAME值为3个字符长度的全部数据。

SELECT * FROM C WHERE NAME LIKE 'a'||chr(95)||'b' ;

--单引号'

SELECT * FROM C WHERE NAME LIKE 'i''m';

SELECT * FROM C WHERE NAME LIKE 'i' || CHR(39) || 'm';

SELECT * FROM C WHERE NAME LIKE 'i\''m' ESCAPE '\';--报错

--符号&

SELECT * FROM C WHERE NAME LIKE '&_hello'; --提示输入变量“_hello”

SELECT * FROM C WHERE NAME LIKE '\&%' ESCAPE '\';--报错

SELECT * FROM C WHERE NAME LIKE CHR(38) || '_hello';

五、去除空格换行

SELECT ID,

NAME,

TRIM(REPLACE(NAME, ' ' )) 去除空格,

REPLACE(NAME, CHR(13), '') 去除回车,

REPLACE(NAME, CHR(10), '') 去除换行

FROM C;

--字符相关函数

LTRIM

LTRIM函数删除字符串左边的前缀字符。一般前缀字符总是空格。

其具体的语法格式如下：

LTRIM(string[,trimchars])

其中：

string 任意VARCHAR2型或CHAR型的数值

trimchars 待删除的字符串

下面是该函数的使用情况：

LTRIM(’ Jeff’)=‘Jeff’(leading spaces removed)

LTRIM(’*****Jeff’,’*’)=‘Jeff’

LTRIM(’*!*!*Jeff’,’*!’)=‘Jeff’

RTRIM

RTRIM函数压缩掉串右面的尾随字符，常常是从指定字符串(第一参数)中压缩空格。

其具体的语法格式如下：

RTRIM(string[,trimchars])

其中：

string任意VARCHAR2型或CHAR型的数据

trimchars准备压缩掉的字符(串)

下面是该函数的使用情况：

RTRIM(‘Jeff ’)= ‘Jeff’(trailing spaces removed)

RTRIM(‘Jenny*****’)= ‘Jeff’

RTRIM(‘Jenny*!*!*’)= ‘Jeff’

TRIM

TRIM函数将字符串的前缀(或尾随)字符删除。

其具体的语法格式如下：

TRIM([LEADING|TRAILING|BOTH][trimchar FROM] string)

其中：

LEADING指明仅仅将字符串的前缀字符删除

TRAILING指明仅仅将字符串的尾随字符删除

BOTH指明既删除前缀字符，也删除尾随字符。这也是默认方式

string任意一待处理字符串

trimchar可选项。指明试图删除什么字符，默认被删除的字符是空格

下面是该函数的使用情况：

TRIM(’ Ashley ’)=‘Ashley’

TRIM(LEADING ’*’ FROM’***Ashley***’)=‘Ashley***’

REPLACE

REPLACE函数在一字符串中搜索一指定的子串，并将其替换成另外一指定串。

其具体的语法格式如下：

REPLACE(string,substring[,replace_string])

其中：

string被搜索字符串。在其中搜索待替换串

substring被搜索子串。所有找到的子串均被替换掉

replace_string替换结果子串。是一可选项。如果该参数被忽略，则所有被搜索到的子串实质上均被删除

下面是该函数的使用情况：

REPLACE(’This is a test’,’is’,’was’)=‘Thwas was a test’

REPLACE(’This is a test’,’is’)=‘Th a test’