Substring

  • 22Views
  • Last Post 3 days ago
  • Topic Is Solved
0
votes
Jens Leweling posted this 3 weeks ago

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 3 weeks ago

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 2 weeks ago

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

 

Thanks

0
votes
Mariia Zaharova posted this 2 weeks ago

Thank you for your reply.

 

Feel free to contact us with any further questions.

 

Best regards,

Mariia

0
votes
Jens Leweling posted this 2 weeks ago

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 2 weeks ago

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 3 days ago

Thanks, that worked.

Close