Thankyou Steve for your quick respnse !!

I am still not able to understand how the case statement is running in this statement. Any help in understanding this would be really helpul

If
set @Provs: 'A|B|C'
SET @Provs=(CASE WHEN @Provs=" " THEN "^" ELSE CONCAT('^',REPLACE(@Provs,"|","$|^"),"$") END);

If the variable has been set to A|B|C then how would the statement run.