ELT to return string based on its position : MySQL Query

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

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

ELT : String from its position

Subhendu Mohapatra