Illuna Illuna - 3 months ago 21
SQL Question

Replace a part of a string using a math factor

I am using PowerShell and MS Access.

I would like to apply a math factor to what is inside the pipes in this column value:

Ex.

Min_Value=|10|;Max_Value=|100|;


With a
*4
factor would become:

Min_Value=|40|;Max_Value=|400|;


I believe I have to

1) use a regular expression to narrow what's inside the pipes,

2) convert it to INT

3) apply the factor

4) replace the string for the UPDATE.

Any help would be great.

Answer

You didn't specify your DBMS so here's a solution using Postgres. At least you can follow the logic behind it:

select 
  concat(
    replace(
      concat('Min_Value=|', string_agg(intval,';')), ';', '|;Max_Value=|'),
    '|;') 
from (
  select (unnest(regexp_matches('Min_Value=|10|;Max_Value=|100|;', 'Min_Value=\|([0-9]+)\|;Max_Value=\|([0-9]+)\|;'))::int*4)::TEXT as intval  
  ) foo;

The magic happens like that:

  1. with regexp_matches extract two integers from input string
  2. use unnest to unpack the array into two separate rows
  3. cast those integer values (which are text at the moment) to Integer and multiply by factor 4
  4. use string_agg with ; as delimiter to make one string row containing both integers delimited with ;
  5. use concat to append Min_Value=| at the beginning, so the string is now Min_Value=|40;400
  6. use replace and replace ; with next part - |;Max_Value=| so the string is now Min_Value=|40|;Max_Value=|400
  7. use concat again to append |; to the end of the string

Input:

Min_Value=|10|;Max_Value=|100|;

Result:

Min_Value=|40|;Max_Value=|400|;