I found using regex_replace(string, search regex, replaced value)
a little very confusing the first time I played with it, so as to avoid making the same mistake twice, here are my notes around this crazy function.
Firstly the replaced value is the opposite to what you are searching for. It sort of makes sense if you consider the replace(string, before, after) function, where the before is what you are searching for, and the after is what is left.
Secondly, you’ll need to double backslash any regex syntax. For example \w, \s, \d.
Assume we have a column called Address, and our data is comma separated: 12 Someplace Street, Some Suburb
We want to create two new columns, one to hold the street name and number and the other the suburb.
To get the street value, we will use:
regexp_replace( “Address” , ‘,([^,]+$)’, ”)
While to get the suburb value, we will use:
regexp_replace( “Address” , ‘^([^,]+,)’, ”)
Anway, I hope this helps shed some light on a potential problem someone is experiencing.