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

ELT(N, ...) returns the Nth string, using positions that start at 1.
We will get null if input number n is less than 1 or more than the number of string available.

SELECT ELT(0,'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=utf8mb4;

--
-- 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);

FIELD to get position of string substring_index to get part of string using delimiter

All String Functions FIELD()

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