Gaurang Jadia / Nothing is permanent except change / Page 11

First of all we have to crate our example table called “Customer”.

CREATE TABLE  `mysql`.`Customer` (
  `CustomerID` int(11) NOT NULL auto_increment,
  `CompanyID` int(11) NOT NULL,
  `FirstName` varchar(45) default NULL,
  `LastName` varchar(45) default NULL,
  `Address` varchar(500) default NULL,
  `City` varchar(45) default NULL,
  `State` varchar(45) default NULL,
  `ZipCode` int(5) default NULL,
  `ContactNumber` int(10) default NULL,
  PRIMARY KEY  (`CustomerID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1

CREATE TABLE `mysql`.`Customer` (

`CustomerID` int(11) NOT NULL auto_increment,

`CompanyID` int(11) NOT NULL,

`FirstName` varchar(45) default NULL,

`LastName` varchar(45) default NULL,

`Address` varchar(500) default NULL,

`City` varchar(45) default NULL,

`State` varchar(45) default NULL,

`ZipCode` int(5) default NULL,

`ContactNumber` int(10) default NULL,

PRIMARY KEY (`CustomerID`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1

We have to insert one record into Customer table. Let’s add Steve Jobs! 🙂

INSERT INTO mysql.Customer(CompanyID, FirstName, LastName, Address, City, State, ZipCode, ContactNumber)
VALUES(1, 'Steve', 'Jobs', '1 Infinity Loop', 'Cupertino', 'CA', 95014, 1112223333);

1 2	INSERT INTO mysql.Customer(CompanyID, FirstName, LastName, Address, City, State, ZipCode, ContactNumber) VALUES(1, 'Steve', 'Jobs', '1 Infinity Loop', 'Cupertino', 'CA', 95014, 1112223333);

Here is syntax to create our function which will bring Concat First and Last Name and return single string.

CREATE DEFINER=`scott`@`%`
FUNCTION  `mysql`.`getCustomerFullName`(intCustomerID INT)
RETURNS varchar(100) CHARSET latin1
BEGIN
  DECLARE strCustomerFullName VARCHAR(100);
  SET strCustomerFullName = CONCAT((SELECT FirstName FROM Customer WHERE CustomerID = intCustomerID),' ', (SELECT LastName FROM Customer WHERE CustomerID = intCustomerID));
  return strCustomerFullName;
END

CREATE DEFINER=`scott`@`%`

FUNCTION `mysql`.`getCustomerFullName`(intCustomerID INT)

RETURNS varchar(100) CHARSET latin1

BEGIN

DECLARE strCustomerFullName VARCHAR(100);

SET strCustomerFullName = CONCAT((SELECT FirstName FROM Customer WHERE CustomerID = intCustomerID),' ', (SELECT LastName FROM Customer WHERE CustomerID = intCustomerID));

return strCustomerFullName;

END

How to call getCustomerFullName() function.

SELECT *, mysql.getCustomerFullName(c.CustomerID) AS 'Full Name' FROM mysql.Customer AS c

1	SELECT *, mysql.getCustomerFullName(c.CustomerID) AS 'Full Name' FROM mysql.Customer AS c

This is how our output will look.

Gaurang Jadia

Nothing is permanent except change

STOP SOPA & PIPA

Our Internet by Google

ProtectIP by Wikipedia

MySQL Create Function Example

How to enable Root User on Mac OS X

Step 1: Go to System Preferences

Step 2: Select Users & Groups under System Tab

Step 3: Select Login Options

Step 4: Click Join next to Network Account Server. It will open Directory Utility dialog box

Step 5: Click Edit Menu from the Menu Bar and click Enable Root User. System will ask you to enter password and it will enable Root User.