Racehorse35 Racehorse35 - 4 months ago 11
SQL Question

MYSQL: Regex substituion

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?

Answer

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';