oracle like 转移,Oracle Like中对转义字符的的处理

结论:

1,oracle 的转义字符是可以自己定义的,格式ESCAPE '/'

2,如果指定转义字符,需要字符串有严格的格式.

3,like内容是大小写敏感的.

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

CREATE TABLE zz_hawk_tab_1406 (

t_num   NUMBER,

t_var   VARCHAR2(100),

t_dat   DATE

)

SELECT * FROM apps.zz_hawk_tab_1406;

--数据如下:

"Row #" t_num t_var t_dat

45.2350646663298845338442459363252131828,"a%","2006年8月4日"

87.5924639192950166471782806410434126723,"b_dddd","2006年8月4日"

26.4340301051836451951308950057417580147,,"2006年8月4日"

40.122026690035404081522998810612162134,"a%a","2006年8月4日"

INSERT INTO apps.zz_hawk_tab_1406 VALUES (dbms_random.value(10,100),'a%',current_date)

INSERT INTO apps.zz_hawk_tab_1406 VALUES (dbms_random.value(10,100),'a%a',current_date)

INSERT INTO apps.zz_hawk_tab_1406 VALUES (dbms_random.value(10,100),'b_dddd',current_date);

INSERT INTO apps.zz_hawk_tab_1406 VALUES (dbms_random.value(10,100),NULL,current_date);

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var LIKE '%'

--没有把null值抓出

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var = 'a%'

--取出了a%

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var like 'a%'

--同样取出了 a% a%a

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var like '%/_%'ESCAPE '/'

--取出 b_dddd

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var like 'a/%'ESCAPE '/'

-- 取出了 a%

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var like 'a_%'ESCAPE '_'

-- 取出了 a%

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var like 'a

-- 取出了 a%

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var like 'ab%'ESCAPE 'b'

-- 取出了 a%

SELECT * FROM apps.zz_hawk_tab_1406 test

WHERE test.t_var like 'ab%'ESCAPE 'a'

-- 这个就会报错,所以说oracle还是很聪明的.

--ORA-01424: missing or illegal character

--following the escape character