ELT : String from its position

SELECT ELT(1,'Str1','Str2','Str3','Str4') // Output Str1

SELECT ELT(3,'Str1','Str2','Str3','Str4') // Output Str3
Return the string based on the input number n
We will get null if input number n is less than 1 or more than the number of string available.
SELECT ELT('Str1','Str2','Str3','Str4') // Output null
SELECT ELT(5,'Str1','Str2','Str3','Str4') // Output null
In our student_name table we will use this to display first name of students.
SELECT ELT( 1, f_name, l_name )as name FROM  `student_name`
name
John
Larry
Ronald
Garry
There are two records at the end with null data. To manage NULL data we can use ifnull or coalesce.
SELECT ELT( 1, ifnull(f_name,'-'), l_name )as name FROM  `student_name`
name
John
Larry
Ronald
Garry
-
-
Using COALESCE
SELECT ELT( 2, IFNULL(f_name,'-'), COALESCE(l_name,'-') ) as name FROM  `student_name`
name
Deo
-
-
Miller
-
Ruller
Here is the sql dump of student_name table for your use.
CREATE TABLE IF NOT EXISTS `student_name` (
  `f_name` varchar(20) DEFAULT NULL,
  `l_name` varchar(20) DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `student_name`
--

INSERT INTO `student_name` (`f_name`, `l_name`, `class`) VALUES
('John', 'Deo', 'Four'),
('Larry', NULL, 'Four'),
('Ronald', NULL, 'Five'),
('Garry', 'Miller', 'Five'),
(NULL, NULL, 'Five'),
(NULL, 'Ruller', NULL);

Your Rating




Post your comments , suggestion , error , requirements etc here .








HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer