Search code examples
mysql

NULL when value not found using substring_index in MYSQL

I am trying to pass out 2 values from a large field within my MYSQL database, to achieve this I am using the substring_index function.

The function works exactly as it should until it comes to a field where the values that I am looking for within the function don't exist. When this happens I just get some random part of the entire field. What I want to happen is if either search value of the function cannot be found, just return NULL.

The code I am using is as follows:

select id,
substring_index(substring_index(field, 'QF=',-1), 'RF=',1) as gscore
from 
tablename

So in short if "QF=" or "RF=" are not within the field then return NULL.

Thanks

Solution

  • You can use an IF function to achieve that.

    select id,
   IF(substring_index(substring_index(field, 'QF=',-1), 'RF=',1)=field,NULL,
   substring_index(substring_index(field, 'QF=',-1), 'RF=',1)) as gscore
from 
tablename