Substring_index in Mysql should return blank string if delimeter not found

Question

 E:g  

 Case 1: delimeter not exist 

 string = "abcdefg"

      mysql> select substring_index(string,'z',1) from table;
      output: abcdefg
      expected output : blank string ""

 Case 2: delimeter exist 

 string = "abczdefg"

       mysql> select substring_index(string,'z',1) from table;
       output: abc
       expected output : abc (same as substring returns)

I want this select query should return me blank string if 'z'(delimeter) is not exist and if delimeter exist in string then whatever substring_index is regular functionality

what modification in Query i can do for my expected output ?

Answer 1

Substring_index does exactly what it should :) For your task you can use substring

set @str = "abcdef";
select substring(@str, 1, LOCATE('z', @str) - 1) from dual;

That will do exactly what you want