Basic Learning for Oracle (III)-Single-line function

One, character functions

1. Casing control function

--lower: Make the letter lowercase----upper: Make the letter uppercase----INITCAP: Make the first letter of the character uppercase--select   lower (' ABC '),  Upper (' sql '),  Initcap (' HeLlo SQL ') from dual;

Select employee_id, Department_id,last_name, salary from  employees where lower (last_name) = ' King ';

2. Character control functions

Concat (STR1,STR2)

Connect two strings.

SUBSTR (Str,index,n)

Intercepts the string, starting at index (the SQL string subscript the first 1) and intercepting n lengths.

Length (str)

Gets the length of str.

InStr (STR1,STR2)

STR2 the first occurrence of the index in STR1, if not present returns 0.

Lpad (STR1,LEN,STR2)

Set the str1 length to Len, if the length is not enough on the left with STR2.

Rpad (STR1,LEN,STR2)

Set the str1 length to Len, if the length is not enough on the right with str2.

Trim (Str1,from str2)

Remove the st1 from the str2, just the first and the tail.

Replace (STR,STR1,STR2)

Replace the str1 in Str with the STR2, all replaced.

Select  concat (' Hello ', ' sql '), substr (' Hellosql ', 2,4), InStr (' HelloWorld ', ' o '), Length (' Hello SQL ') from dual;

Select Employee_id,last_name, Salary,lpad (salary,10, ' * ') from  employees where department_id=80;

--trim: Just remove the first composite----Replace: Replace all--select  trim (' A ' from ' AABBAACCAA '), replace (' AABBAACCAA ', ' a ', ' M ') from dual;

Second, the number function

Round: Rounding function.

Trunc: Truncation function.

MoD: Find the remainder function.

--round: Rounded--select    round (435.45,1),   round (435.45),   round (435.45,-1) from dual;

--trunc: Truncate--select    trunc (435.45,1),   trunc (435.45),   trunc (435.45,-1) from dual;

Basic Learning for Oracle (III)-Single-line function