sqlprestodbeavertrino

what is the equivalent of Substitute function in excel to presto sql

what is the equivalent of substitute function of excel to presto sql. I have code here that i need to convert to presto sql. below is the code and sample data.

excel code
=CONCAT(SUBSTITUTE(B2,LEFT(B2,11),MID(B2,5,3)&"-"&MID(B2,8,2)&"10-"))

string to be converted -------output result
-----------------------------------------------
JUNE5000950ABS20MN9G250GRN----500-0910-ABS20MN9G250GRN
Solution

  • One way to do this is by using concatenation + substring:

    -- sample data
WITH dataset(str) as (
    values ('JUNE5000950ABS20MN9G250GRN')
)

-- query
select substr(str, 5, 3) || '-' || substr(str, 8, 2) || '10-' || substr(str, 12)
from dataset;

    Output:

              _col0
--------------------------
 500-0910-ABS20MN9G250GRN

    Another approach can be to use regular expressions with regexp_replace (explanation @regex101):

    select regexp_replace(str, '^.{4}(\d{3})(\d{2}).{2}(.*)', '$1-$210-$3')
from dataset;