Substring

  • 25Views
  • Last Post 19 May 2019
  • Topic Is Solved
0
votes
Jens Leweling posted this 07 May 2019

I'm connecting to Magento and the BilingStreet returns values in the following format: ["Homburger Str. 6"] -- I would like to use a substring expression to remove the [" and "] .

How would I use the SUBSTRING expression to make this happen?

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 07 May 2019

Hi Jens!

You can try Expression like this one:

SUBSTRING(name,(FINDSTRING(name,"[\"",1)+2),(FINDSTRING(name,"\"]",1)-FINDSTRING(name,"[",1)-2))

 

Please tell me if this helps.

 

 

0
votes
Jens Leweling posted this 07 May 2019

Hi Mariia, thanks for your quick help. It seems to have worked. 

 

Thanks

0
votes
Mariia Zaharova posted this 08 May 2019

Thank you for your reply.

 

Feel free to contact us with any further questions.

 

Best regards,

Mariia

0
votes
Jens Leweling posted this 10 May 2019

Hi Mariia,

The substring formula is working. I now have one more issue. It seems that Magento sometimes concatenates two fields. In that case it the value looks the following:

Schillerstr.","76    

I need to extend the current expression to find "," and replace it with a space. Could you help me with that one as well?

 

SUBSTRING(BillingAddressStreet,

(FINDSTRING(BillingAddressStreet,"[\"",1)+2),

(FINDSTRING(BillingAddressStreet,"\"]",1)-

FINDSTRING(BillingAddressStreet,"[",1)-2))

 

Thanks

Jens

 

 

0
votes
Mariia Zaharova posted this 14 May 2019

Hi! Please try this one:

REPLACE(SUBSTRING(BillingAddressStreet,

(FINDSTRING(BillingAddressStreet,"[\"",1)+2),

(FINDSTRING(BillingAddressStreet,"\"]",1)-

FINDSTRING(BillingAddressStreet,"[",1)-2)),"\",\""," ")

 

Best regards,

Mariia

0
votes
Jens Leweling posted this 19 May 2019

Thanks, that worked.

Close