如何转义&

iamloki

SELECT *   FROM A where A.col1 = 'F&ORG'

用shell调sqlplus，要查的是col1值为'F&ORG'，但是总是提示输入变量&org

whbilly

SQL> insert into test000 values('scott'||chr(38)||'tiger');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test000;

username
----------------------------------------
scott&tiger

旋律de茶叶

SELECT *   FROM A where A.col1 = 'F' || chr(38) ||'ORG'

idpublic

where A.col1 ='F'||'&'||'ORG'

这样行不

whbilly

同样可以的

SQL> insert into test000 values('F'||'&'||'ORG');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test000;

username
----------------------------------------
scott&tiger
F&ORG

idpublic

CREATE TABLE t_01(col1 VARCHAR2(20))
INSERT INTO t_01 VALUES ('F'||'&'||'ORG')
SELECT * FROM t_01
SELECT * FROM t_01 WHERE col1='F'||'&'||'ORG'

我这里这样倒是可以的

iamloki

没有转义符吗。。。
这样拼凑很麻烦阿

whbilly

SQL> select * from test000 where "username"='scott'||chr(38)||'tiger';

username
----------------------------------------
scott&tiger

SQL> select * from test000 where "username"='F'||'&'||'ORG';

username
----------------------------------------
F&ORG

idpublic

zt

回复： 请教：SQL语句中转义符是那个？


我知道了，在sql*plus中使用转义符'\'，要首先修改环境变量
set escape on
随后就可以使用转义符'\'了，例如
SQL>set escape on
SQL>select count(*) from employees where name='\[$1']
   COUNT(*)
----------
          0
SQL>select count(*) from employees where name='&1'；
  
输入 1 的值:  a
  
原值    1: select count(*) from employees where name='&1'
新值    1: select count(*) from employees where name='a'
  
  COUNT(*)
---------
         0

iamloki

楼上正解

SQL> set escape on
SQL> select 'F&org' from dual;
Enter value for org:
old   1: select 'F&org' from dual
new   1: select 'F' from dual

'
-
F

SQL> select 'F\&org' from dual;

'F&OR
-----
F&org

SQL>


谢谢大家回答