How to escape&

iamloki

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

Use shell to call sqlplus. The value to be checked is that col1 is 'F&ORG', but it always prompts for the variable&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'

Does this work

whbilly

Also possible

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'

I can do this here

iamloki

Is there no escape character? . .
This is very troublesome to put together

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

Reply: Ask: Which is the escape character in the SQL statement?


I know, to use the escape character '\' in sql*plus, we must first modify the environment variables
set escape on
Then you can use the escape character '\', for example
SQL>set escape on
SQL>select count(*) from employees where name='\[$1']
   COUNT(*)
----------
          0
SQL>select count(*) from employees where name='&1';
  
Enter a value of 1: a
  
Original value 1: select count(*) from employees where name='&1'
New value 1: select count(*) from employees where name='a'
  
  COUNT(*)
---------
         

iamloki

Positive solution upstairs

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>


Thanks everyone for answering