So Im going to try and explain what Im trying to accomplish.
I'm looking to copy a string from FIELD1 to FIELD2, make it uppercase, and depending on the value of another field (FIELD3: yes/no), either prepend "HOST-" or append "-NET" to FIELD2. Also, this also only needs to affect entries whose FIELD4 value is "bar".
So for example FIELD1 contains the text "foo" and its Host value is "yes". FIELD2 should read "HOST-FOO".
For FIELD3 value "no", FIELD2 would read "FOO-NET".
My coworker and I were talking about this and he believes this can be accomplished via regex. Am I in the right ballpark?
If i understand correctly your question i think is more simple use CASE
select case field3 when 'yes' then concat( 'HOST-' . field1) when 'no' then concat( field1 .'-NET') END as Field2 from my_tbale where field4 = 'bar';