Login
extension joomla, template joomla,banner extension joomla,jomla slider,slider joomla

ELT to return string based on its position : MySQL Query

Hits: 647
ELT : String from its position

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








Related Article


Project to update domain & userid part of an email address by single query for Mysql table
STRCMP String comparison two strings MySQL Query
RTRIM to remove blank space from RIGHT of main string by MYSQL query
LTRIM to remove leading blank space from main string by MYSQL query
FIELD to get MySQL Query
ELT to return string based on its position : MySQL Query

destination source:https://www.plus2net.com/sql_tutorial/string-elt.php

Tutorials Menu