Laravel, Eloquent: SQL query with left join

If the query needs parentheses/brackets for a where condition like below Normal SQL:

SELECT users.fname, planets.name 
FROM  users
LEFT JOIN  planets ON (users.planet_id = planets.id)
WHERE users.lname LIKE '%hawk%' 

ELOQUENT SQL:

$name = "hawk";

$userResults = DB::table('users')
       ->leftJoin('planets', function($join) {
         $join->on('users.planet_id', '=' , 'planets.id');
        })
       ->where('users.lname' , 'like', '%'.$name.'%')
       ->get(array('users.fname', 'planets.name'));

Eloquent: SQL Query with where or in brackets

If the query needs parentheses/brackets for a where condition like below Normal SQL:

SELECT * 
FROM  users
WHERE planet = 'earth'
AND (fname LIKE '%hawk%' OR lname LIKE '%hawk%')
AND state = 'FL'

ELOQUENT SQL:

$name = "hawk";

$usersResults = DB::table('users')
    ->where('planet', '=', 'earth')
    ->where(function($query) use ($name){
            $query->where('fname' , 'like', '%'.$name.'%');
            $query->orWhere('lname' , 'like', '%'.$name.'%');
      })
     ->where('state', '=', 'FL')
    ->get();

Oracle: Split a String Based on Delimiter

We can split a string based on Delimiter using a combination of  INSTR and SUBSTR functions:

INSTR Returns the position of a String within a String. For more information see Oracle instr function
SUBSTR Returns a substring. For more information see Oracle substring
Syntax of SUBSTR:

SUBSTR([input],[start],[length])  

Example 1:

 
select substr('orange.apple',1,(instr('orange.apple','.')) - 1)  
 from  dual  

Output:
first

Example 2:

 
select substr('orange.apple',1,(instr('orange.apple,'.')) - 1) as First,  
        substr('orange.apple', (instr('orange.apple','.')) + 1) as Second  
 from  dual 

Output:
First         Second
orange           apple

MySQL: Calculate DATE, TIME difference in SQL statement using MySQL DB

Following examples showing how to calculated DATE, TIME differences in SQL statement in MySQL database.

Functions used in the following examples are TIMEDIFF and DATEDIFF

 select TIMEDIFF('2011-10-31 12:50:56' , '2011-09-23 12:50:21')   
 The above outputs : 838:59:59  

 select TIMEDIFF('15:50:56' , '12:55:21')   
 The above SQL outputs: 02:55:35  

 select DATEDIFF('2011-10-31 12:50:56' , '2011-09-23 12:50:21')   
 The above SQL outputs: 38  

 select DATEDIFF('2011-10-31' , '2011-09-23')   
 The above SQL outputs: 38  

SQL : Update only year in the date field

UPDATE mytable
SET datefield = CONCAT('2012','-',MONTH(datefield),'-',DAYOFMONTH(datefield))
WHERE YEAR(datefield) = 2006;