Common Oracle SQL

Tablea Columna columnb

A 1 & 2 SSS

 

Select * from a where Columna like '% 1 & 1000'; ------------------ the system prompts you to enter a variable.

What if I want to query SSS Based on the clomna value?

Analysis: & is a variable character (special character) in Oracle ). Therefore, you must set & to a non-special character for normal query.

Solution: set define off

Select * from a where Columna like '1 & 2 ';

 

 

Set define off explanation:

Set define off disables the substitution variable function. The default "&" in SQL * Plus indicates the substitution variable, that is, as long as the symbol appears in the command, SQL * Plus requires you to enter an alternative value. This means that you cannot input a string containing this symbol into the database or assign it to a variable. For example, the string "SQL & plus" system will understand it as a string with the "SQL" header, it prompts you to enter the value of the substitution variable plus. If you enter ABC, the final string is converted to "sqlabc ". Set define off disables this function. "&" is used as a common character. In the preceding example, the final character is "SQL & plus" set define.
Off disable substitution variable function set define on enable substitution variable function set define * replace variable flag as "*" by default (can also be set to other characters)