REGEXP_SUBSTR(‘4550 Montgomery AvenueTorontoOntarioM4A 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.
You’d do it with multiple calls. REGEXP_SUBSTR cannot return multiple values.
I want multiple calls.
one separate call for address
one separate call for city
one separate call for state
one separate call for Postal code
txs
So you tell me.
What’s the regex for the state, if my string is SomeRandomStreet*SomeRandomCity*SomeRandomState*SomeRandomPostCode*
?
Note: Keep in mind that * is a special character in regex, so it will need to be escaped.
the value for state will be “SomeRandomState” in your case.
“SomeRandomStreetSomeRandomCitySomeRandomStateSomeRandomPostCode”
My string is always in this format “addresscitystatePostal code” where state is the value after second * till the third *(excluding *).
oh derp. Apologies. I confused you with someone else who was asking about REGEXP_SUBSTR in another thread. That person would have already known. Again, apologies. I will assume you’re new to RegExp.
The street is [^\*]* (You dont need the other parameters, because you would use their default values, 1,1)
The city is [^\*]*,1,3
The state is [^\*]*,1,5
and the postcode is [^\*]*,1,7
Your statements do not work.
Tried the following statement and it doesn’t work - Returns nothing/blank:
REGEXP_SUBSTR(‘4550 Montgomery AvenueTorontoOntarioM4A 2S3’, ‘[^*]*’)
You are using MySQL 8, right?
Works for me…
Make sure you’re using MySQL 8, and that if you’re doing this on a Mac, that when you’re copying and pasting things, the Mac isnt doing silly things like replacing normal quotes with tilted ones that MySQL isnt going to like.
Txs, It works.