Re: Mixed Case Substring

[Jennifer Ford]

Sample query (I took out the bq part so just querying this one table)

select
order_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(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 'Inception EsiId',
SUBSTRING_INDEX(IF(LOCATE('[EsiId] =>',bq.request_data)=0,'NA',SUBSTRING_INDEX(request_data,'[EsiId] =>',-1)),'\n',1) AS 'Request EsiId'

from database1.table1
where a.id = '4063744'
and request_data like '%InfoRequest%'



Wasn't sure how to add a text file to show the table so here is copy & paste
id order_id customer_id request_date request_data response_date response_data
1 4063744 34614999 4/8/2019 11:07 "stdClass Object
(
[InfoRequest] => stdClass Object

(
[ApplicantInfo] => stdClass Object
(
[EsiIdShownAsRisk] =>
[CompanyName] =>
)

[EsiId] => 100891350117

[MailingAddress] => stdClass Object
(
[StreetNum] => 9999
[Street] => MAIN ST
[UnitNr] =>
[ZipCode] => 77008
)

[esiid] => 10082589999550117
[blue] =>

)

)" 4/8/2019 11:07 "stdClass Object
(
[InfoRequestResult] => stdClass Object
(
[Success] => 1
)

)"
2 4063745 34615000 4/8/2019 11:23 "stdClass Object
(
[InfoRequest] => stdClass Object

(
[ApplicantInfo] => stdClass Object
(
[EsiIdShownAsRisk] =>
[CompanyName] =>
)

[EsiId] => 100891350117

[MailingAddress] => stdClass Object
(
[StreetNum] => 9999
[Street] => MAIN ST
[UnitNr] =>
[ZipCode] => 77008
)

[esiid] => 10082589999550117
[blue] =>

)

)" 4/8/2019 11:23 "stdClass Object
(
[InfoRequestResult] => stdClass Object
(
[Success] => 1
)

)"
3 4063746 34615001 4/8/2019 11:52 "stdClass Object
(
[InfoRequest] => stdClass Object

(
[ApplicantInfo] => stdClass Object
(
[EsiIdShownAsRisk] =>
[CompanyName] =>
)

[EsiId] => 558745569

[MailingAddress] => stdClass Object
(
[StreetNum] => 7225
[Street] => MAIN ST
[UnitNr] =>
[ZipCode] => 77008
)

[esiid] => 55874445604895569
[blue] =>

)

)" 4/8/2019 11:52 "stdClass Object
(
[InfoRequestResult] => stdClass Object
(
[Success] => 1
)

)"