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);
← SQL String References
FIELD to get position of string →
substring_index to get part of string using delimiter→
← Subscribe to our YouTube Channel here