Mixed Case Substring
Hi,
I have to search for content that is within a single field. My IT folks gave me two versions. The first is for when the substring is listed more than once and I need the value associated with a specific response (or if the field is "1 long line of content").
1. SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(bq.request_data, -- field name to search in
'[CompanyName]',-1), -- a unique label before your item
'[MailingAddress]',1), -- what is directly following your item
'[EsiId] => ',-1),'\n',1 -- what is directly before your item
) as 'EsiId',
2. SUBSTRING_INDEX(IF(LOCATE('[EsiId] =>',bq.request_data)=0,'NA',SUBSTRING_INDEX(bq.request_data,'[EsiId] =>',-1)),'\n',1) AS 'Request EsiId',
The trouble is that this same IT team keeps changing up how they enter the substring - sometimes it is "ESIID" sometimes "EsiID" sometimes "essiid", etc.
I don't want to have to enter a substring_index line for each variation of the upper/lower case of the string I am looking for, and then figure out a way to merge all the good results only to a single field.
How can I search for "any version of XXXXX" (in this case any version of esiid) in one line by inserting something into each of the examples above?
I am also curious if you know how to modify the first option to include a "if you don't find it, give me xxx" response like the 2nd version has. I know it can be done but I have lost my samples that included it.
Thanks!
JFo