Oracle single row Functions

Single Row Function Format:

Function name [(parameter 1, parameter 2, parameter 3 ,...)]

The parameters can be user-defined constants, variables, column names, and expressions.

A single-row function operates only one row of data in the table, and generates only one output result for each row of data.

A single-row function can be used in clauses of select, where, and order by, and a single-row function can be nested.

A single-row function contains the struct type. Numeric, date, conversion, and general functions.

1. Single Row Functions

(1) lower (column name | expression): This function is used to convert characters into lowercase letters.

Select lower ('SQL: Structural query color ')
From dual;

Dual is a virtual table (pseudo table) of the system. The reason is:

The query statement must contain two clauses: select and from. However, lower ('SQL: Structural query language') does not belong to any table, therefore, the virtual table dual provided by Oracle is used to solve this problem.

(2) upper (column name | expression): This function is used to convert characters into uppercase letters.

Select upper ('SQL is used exclusively in rdbms ')
From dual;

(3) initcap (column name | expression): This function is used to convert the first character of each word into uppercase and lowercase letters.

Select initcap ('SQL is an English like language ')
From dual;

(4) Concat (column name | expression, column name | expression): This function is used to concatenate 1st strings and 2nd strings into one string.

Select Concat ('SQL alows you to manipulate the data in db', 'without any programming knowledge ')
From dual;

(5) substr (column name | expression, M, [N]): This function is used to return the specified substring. The substring starts from the M character and its length is N.

Select substr ('SQL lets you concentrate on what has to be done', 14)
From dual;

(6) length (column name | expression): This function is used to return the length of a string in a column or expression.

Select length ('SQL does no let you concentrate on how it will be achieved ')
From dual;

(7) instr (column name | expression, 'string', [m], [N]): This function is used to return the numeric position of the given string, M indicates the number of times the given string appears, and the default value is 1.

Select instr ('SQL allows for dynamic dB changes', 'F ')
From dual;
Select instr ('SQL allows for dynamic dB changes', 'F ')
From dual;

(8) TRIM ([leading | trailing | both] character to be removed from the source string): this function can be used from the leading and trailing in the source string) remove "the string to be removed" from the header or tail (both ". If no header or tail is specified, the trim function is processed by default (both). (This function is introduced by 8i and is used by ltrim and rtrim in versions earlier than 8i ).

Select trim ('? 'From '? SQL * Plus is the SQL implementation used in an Oracle RDBMS or ordbms .')
From dual;
Select trim ('? 'From' it can process data in sets of rows ?? ')
From dual;
Select trim ('s 'from' SQL * Plus is a fourth generation query language ')
From dual;
Select trim (trailing's 'from' SQL * Plus is a fourth generation query ages ')
From dual;

(9) Replace (body expression, string to be searched, replace string): This function is used to search for "string to be searched" in "body expression ", if it is found, replace it with the replacement string.

Select Replace ('SQL * Plus supports loops or if statements', 'supports', 'does not support ')
From dual;

Instance:

Select empno as "code", upper (ename) Name, initcap (job) "job"
From EMP
Where lower (job) = 'salesman ';

Select Concat (ename, job) "employee", substr (job, 1, 5) "title", length (ename) "length", instr (job, 'M ')
From EMP
Where lower (job) = 'salesman ';

2. Numeric Functions

Round (column name | expression, n): This function rounds the column name or the value represented by the expression to N digits of the decimal point.

Trunc (column name | expression, n): This function takes the value represented by the column name or expression to the last n digits of the decimal point.

MoD (m, n): This function divides m by N and returns the remainder.

Select round (168.888, 1), trunc (168.888, 1)
From dual;

Select round (168.888), trunc (168.888)
From dual;

Select round (168.888,-1), trunc (168.888,-1)
From dual;

Select Mod (1, 1900,400)
From dual;

3. Date data

The internal storage format of Oracle date data is century, month, day, hour, minute, and second.

From Oracle9i, the default format of date data input and output is DD-MON-RR, and DD-mon-yy in earlier versions.

If you are using a Chinese operating system and the character set of the database is American English, to make the display of date Data correct, enter the following command:

Alter session set nls_date_language = 'American ';

Oracle provides the system date function sysdate, which returns the current system time.

Select sysdate from dual;

Add or subtract a date data and a number, and the result is still a date data.

Select a sysdate-10 from dual;
Select sysdate + 10 from dual;

The date data is subtracted and the result is a number. To_date converts a string to a date type data.

Select to_date ('15-JUL-02 ')-sysdate from dual;

After a date data is added or subtracted from an hour and divided by 24, the result is still a date data type.

Select sysdate-22/24 from dual;

Select empno, ename, job, Sal, (sysdate-hiredate)/365 "years" from EMP where job like 'sal % ';