Concat

Description:

CONCAT function allows you to concatenate only two strings together.




syntax:

Concat(char1,char2);

Concat returns char1 concatenated with char2.

Both char1 and char2 can be any of the datatypes CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. 

The string returned is in the same character set as char1.

Its datatype depends on the datatypes of the arguments.

In concatenations of two different datatypes, Oracle Database returns the datatype that results in a lossless conversion.

Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national datatype, then the returned value is a national datatype.

For example:

·         CONCAT(CLOB, NCLOB) returns NCLOB




·         CONCAT(NCLOB, NCHAR) returns NCLOB




·         CONCAT(NCLOB, CHAR) returns NCLOB

·         CONCAT(NCHAR, CLOB) returns NCLOB




This function is equivalent to the concatenation operator (||).

examples:

select concat('Connect ','gad') 

from dual;

select concat(concat('Connect ','gad'),' blog') 

from dual;

INITCAP

Description:

INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2.

The return value is the same datatype as char.

Syntax:

INITCAP(char)

Examples:

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  last_name = 'higgins';

0 rows selected

The select statement displays the employee number, name, and department number of employee Higgins.

The WHERE clause specifies the employee name 

as higgins.

Because all the data in the EMPLOYEES table is stored in proper case, the name higgins does not find a match in the table,and no rows are selected.

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  last_name = INITCAP('higgins');

The WHERE clause specifies that the employee name in the EMPLOYEES table is compared to INITCAP('higgins') which equal 'Higgins'.

Because both names are now 'Higgins', a match is found and one row is selected.

UPPER

Description:

UPPER returns char, with all letters uppercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
The return value is the same datatype as char.

Syntax:

UPPER(char)

Examples:

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  last_name = 'HIGGINS';

0 rows selected

The select statement displays the employee number, name, and department number of employee Higgins.

The WHERE clause specifies the employee name 

as HIGGINS.

Because all the data in the EMPLOYEES table is stored in proper case, the name HIGGINS does not find a match in the table,and no rows are selected.

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  UPPER(last_name) = 'HIGGINS';

The WHERE clause specifies that the employee name in the EMPLOYEES table is compared to HIGGINS, converting the LAST_NAME column to uppercase for comparison purposes.

Because both names are now uppercase, a match is found and one row is selected.

LOWER

Description:

LOWER returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
The return value is the same datatype as char.

Syntax:

LOWER(char)

Examples:

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  last_name = 'higgins';

0 rows selected

The select statement displays the employee number, name, and department number of employee Higgins.

The WHERE clause specifies the employee name 

as higgins.

Because all the data in the EMPLOYEES table is stored in proper case, the name higgins does not find a match in the table,and no rows are selected.

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  LOWER(last_name) = 'higgins';

The WHERE clause specifies that the employee name in the EMPLOYEES table is compared to higgins, converting the LAST_NAME column to lowercase for comparison purposes.

Because both names are now lowercase, a match is found and one row is selected.

SELECT first_name, job_id FROM EMPLOYEES
WHERE department_id = 60;

The original data from EMPLOYEES table.

SELECT LOWER(first_name),LOWER(job_id) FROM EMPLOYEES
WHERE department_id = 60;

The same data using LOWER function.

DEFINE Command

Description:
Use the DEFINE command to create and assign a value to a variable.
Use the UNDEFINE command to remove a variable.

Examples:

DEFINE employee_num = 200

SELECT employee_id, last_name, salary

FROM   employees

WHERE  employee_id = &employee_num ;

UNDEFINE employee_num

The example creates a substitution variable for an employee number by using the DEFINE command. At run time, this displays the employee number, name and salary for that employee.

Because the variable is created using the SQL Developer DEFINE command, the user is not prompted to enter a value for the employee number. Instead, the defined variable value is automatically substituted in the SELECT statement.

The EMPLOYEE_NUM substitution variable is present in the session until the user undefines it or exits the SQL Developer session.

Substitution Variables

Description:

Suppose that you want a query that lists the employees with various jobs and not just those whose job_ID is SA_REP.
You can edit the WHERE clause to provide a different value each time you run the command, but there is also an easier way.

By using a substitution variable in place of the exact values in the WHERE clause, you can run the same query for different values.
  
You can create reports that prompt users to supply their own values to restrict the range of data returned, by using substitution variables.

You can embed substitution variables in a command file or in a single SQL statement. A variable can be thought of as a container in which values are temporarily stored. When the statement is run, the stored value is substituted.

You can use single-ampersand (&) substitution variables to temporarily store values.

You can also predefine variables by using the DEFINE command. DEFINE creates and assigns a value to a variable.

Examples:

SELECT employee_id, last_name, salary, 

FROM   employees

WHERE  employee_id = &employee_num ;

The example creates a SQL Developer substitution variable for an employee number.

When the statement is executed, SQL Developer prompts the user for an employee number and then displays the employee number, last name, salary for that employee.

With the single ampersand, the user is prompted every time the command is executed if the variable does not exist.

SELECT last_name, department_id, salary*12

FROM   employees

WHERE  job_id = '&job_title' ;

In a WHERE clause, date and character values must be enclosed with single quotation marks. The same rule applies to the substitution variables.

Enclose the variable with single quotation marks within the SQL statement itself.

SELECT employee_id, last_name, &column_name

FROM   employees

WHERE  &condition

ORDER BY &order_column ;

You can use the substitution variables not only in the WHERE clause of a SQL statement, but also as substitution for column names, expressions, or text.

SELECT   employee_id, job_id,&&column_name

FROM     employees

ORDER BY &column_name ;

You can use the double-ampersand (&&) substitution variable if you want to reuse the variable value without prompting the user each time. The user sees the prompt for the value only once.

In this example the user is asked to give the value for the variable, column_name, only once. The value that is supplied by the user (department_id) is used for both display and ordering of data. If you run the query again, you will not be prompted for the value of the variable.

UNDEFINE column_name

SQL Developer stores the value that is supplied by using the DEFINE command; it uses it again whenever you reference the variable name. After a user variable is in place, you need to use the UNDEFINE command to delete it.

Comparison Operators

 

Description:

Comparison operators are used in conditions that compare one expression to another value or expression. They are used in the WHERE clause.

Syntax:

... WHERE expr operator value

Examples:

SELECT last_name, salary

FROM   employees

WHERE salary <= 3000;

The SELECT statement retrieves the last name and salary from the EMPLOYEES table for any employee whose salary is less than or equal to $3,000. Note that there is an explicit value supplied to the WHERE clause. The explicit value of 3000 is compared to the salary value in the SALARY column of the EMPLOYEES table.

SELECT last_name, salary

FROM   employees

WHERE  salary BETWEEN 2500 AND 3500 ;

The SELECT statement returns rows from the EMPLOYEES table for any employee whose salary is between $2,500 and $3,500.

Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first.

SELECT employee_id,last_name, salary, manager_id

FROM   employees

WHERE  manager_id IN (100, 101, 201) ;

The SELECT statement displays last names, salaries, and managers’ employee numbers for all the employees whose manager’s employee number is 100, 101, or 201.

The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3.

SELECT last_name

FROM   employees

WHERE  last_name LIKE '_o%' ;

 

% denotes zero or many characters.

 _ denotes one character.

The SELECT statement displays the names of all employees whose last names have the letter "o" as the second character.

SELECT last_name, manager_id

FROM   employees

WHERE  manager_id IS NULL ;

 

A null value means that the value is unavailable, unassigned, unknown, or inapplicable. Therefore, you cannot test with =, because a null cannot be equal or unequal to any value.

The SELECT statement displays the last names and managers of all employees who do not have a manager.