SQLCommands

Some most common SQL commands: TRUNCATE, ROUND, INSTR, SUBSTR, POWER, TRIM, SQRT, Replace, MOD, Length ....

TRUNC function in Oracle(TRUNCATE in MySQL):

TRUNC(number, digits after decimal)

Select TRUNC(5.3278,2) from Dual;

Output = 5.32

Select TRUNC(5.3278,0) from Dual;

Output = 5

Select TRUNC(5.3278) from Dual;

Output = 5

Select TRUNC(5.3278,1) from Dual;

Output = 5.3

Select TRUNC(5.3278,1) from Dual;

Output = 5.3

Select TRUNC(235.32,-1) from Dual;  //-ve means before decimal

Output = 230

Select TRUNC(235.32,-2) from Dual; 

Output = 200

Select TRUNC(235.32,-3 or greater negative value) from Dual; 

Output = 0

 

Syntax in MySQL:

Select TRUNCATE(235.32,-2) from Dual; 

Output = 200

NOTE: instead of DUAL, you can use the table name also, you can try that.

 

TRUNC in DATE

TRUNC(DATE);

TRUNC(Sysdate, ‘Month’);

TRUNC(Sysdate,’year’);

TRUNC(Sysdate,’w’);

TRUNC(Sysate,’ww’);

 

ROUND in SQL

Select ROUND(235.725,2) from dual ;

Output = 235.73

Select ROUND(235.724,2) from dual ;

Output = 235.72

Select ROUND(235.32,-1) from dual ;

Output = 240

Select ROUND(234.32,-1) from dual ;

Output = 230

Select ROUND(299.32,-2) from dual ;

Output = 300

Select ROUND(234.32,-1) from dual ;

Output = 230

 

 

INSTR in SQL

INSTR(‘String’ , CharToSearch) = Position of the char starting from the 1^st char

INSTR(‘String’ , CharToSearch, StartPoint)

INSTR(‘String’ , CharToSearch, StartPoint, Occurance)

INSTR(‘UnderUmbrella’ , ’b’) = 8

INSTR(‘UnderUmbrella’ , ’r’ , 1) = 5 //start from the 1^st character

INSTR(‘UnderUmbrella’ , ’r’ , 6) = 9 //it starts from 6^th character

INSTR(‘UnderUmbrella’ , ’e’ , 2, 1) = 4   //Start from 2 and get the first occurrence of ‘e’

INSTR(‘UnderUmbrella’ , ’e’ , 2, 2) = 10   //Start from 2 and get the second occurrence of ‘e’

 

SUBSTR in SQL

SUBSTR(‘UnderUmbrella’ , 6) = Umbrella  //Print all chars starting from 6^th char

SUBSTR(‘UnderUmbrella’ , 6,3) = Umb //Print 3 chars starting from 6^th char

SUBSTR(‘UnderUmbrella’ , -6) = brella  //Print all chars starting from 6^th char from the last

SUBSTR(‘UnderUmbrella’ , -6,3) = bre //Print 3 chars starting from 6^th char from the last

 

TRIM in SQL

TRIM(‘  ABC  ‘) = removes the space from both side

LTRIM(‘  ABC  ‘) = removes the space from left side only

RTRIM(‘  ABC  ‘) = removes the space from right side only

 

Some other SQL Commands:

POWER in SQL

POWER(3,2) = 3² = 9

SQRT(25) = 5

MOD(5,2) = 1

MOD(n,2)=0 , this means n is even number

MOD(n,2)=1 , this means n is odd number

Length(‘Hello’) = 5

Replace(‘abcde’, ‘a’ , ‘A’)  = Abcde

Replace(‘abcde’, ‘de’ , ‘abc’) = abcabc

Replace(‘abcde’, ‘c’, ‘ ‘) = ab de //'c' is replace by a space

Replace(‘abcde’, ‘a’) = bcde  //null case

Replace(‘abcde’, ‘c’) = abde //null case