How can I extract address, city, state and Postal code from a text separated by * using REGEXP_SUBSTR in MySQL?

[Muhammad Akhtar]

How can I extract address, city, state and Postal code from a text separated by * using REGEXP_SUBSTR in MySQL?


REGEXP_SUBSTR(‘4550 Montgomery Avenue*Toronto*Ontario*M4A 2S3*’, ‘(.? ){2}(.?),’)

I get a list of dynamic text which contains address, city, state and Postal code separated by *. Need to exact address, city, state and Postal code using REGEXP_SUBSTR.

I want multiple statements.
one separate call for address
one separate call for city
one separate call for state
one separate call for Postal code


My string is always in this format “address*city*state*Postalcode*” where state is the value after second * till the third *(excluding *).
txs