REPLACE ( string_expression , string_pattern , string_replacement )

[MarioVanDenEijnde]

Hello ALASQL team,

Does ALASQL support a replace function like: REPLACE ( string_expression , string_pattern , string_replacement )

Kind regards, Mario

[MarioVanDenEijnde]

I looked at the user defined functions and came with the following which works fine:

alasql.fn.replacestr = function(a,b,c) { return a.replace(b, c);}

Kind regards, Mario

[agershun]

Mario, there is a problem in your code - it will work only one time per string. It is better to do something like:

alasql.fn.replacestr = function (str,pattern,newstr)
     var re = new RegExp(pattern,"g"); // or "gi"
    return (str||'').replace(re,newstr)
};

I will add this function to the next version.

[mathiasrw]

:+1:

[MarioVanDenEijnde]

Hello Andrey,

You left out an "{" But could you add the select string also:

alasql.fn.replacestr = function (str,pattern,newstr) {
     var re = new RegExp(pattern,"g"); // or "gi"
    return (str||'').replace(re,newstr)
};

I need to replace a dot with an underscore and below does not work: Invalid flags supplied to RegExp constructor '_'

replacestr([Name],".","_") AS wName

Kind Regards, Mario

[agershun]

In RegExp you need to add \ in the pattern before special symbols like \.,\_ or \.\_

[agershun]

Please, see Regular Expression manual. It is a little bit complicated, but very powerful...

[MarioVanDenEijnde]

Hello Andrey,

I came up with below but I keep getting back: Invalid flags supplied to RegExp constructor '_' But I need to use double quotes or am I wrong?

replacestr([ImageName],"/\.([^.]+)$/", "_") AS wName

Also tried:

"\_"

[mathiasrw]

Please remember that the string also needs to escape the back slash so to get dot in the regex you need to escape it and escape the escape: \\.

And then there is that - at least for string we need an extra escape. Can't test it now bit if previous does not work try \\\\. To get a dot in regex

[MarioVanDenEijnde]

Tried also below: now I get "Parse error on line 1:" Are we sure the error is in the expression and not in the function?

replacestr([Name],\.,\_) AS wName

replacestr([Name],\\.,\\_) AS wName

[agershun]

AlaSQL still does not support RegExp, so we need to put pattern expression in quotes:

replacestr([Name],"\.","_") AS wName
replacestr([Name],"\\.","_") AS wName

I will write correct (like-MySQL) `REPLACE()`` function tonight without any RegExp. Now you can use temporary solution like:

alasql.fn.myreplacestr1 = function(s) {
    return (s||'').replace(/\./g,"_");
};

[MarioVanDenEijnde]

Thanks.

[agershun]

Done. I will upload it right now. Here is the code of standard function:

alasql.fn.REPLACE = function (target,pattern,replacement) {
    return target.split(pattern).join(replacement);
};

[MarioVanDenEijnde]

Just to be sure: I have to download the latest version? And I can use the REPLACE without calling a user defined function?

Kind regards, Mario

[mathiasrw]

Yes, https://github.com/agershun/alasql/blob/develop/dist/alasql.js now supports REPLACE ( string_expression , string_pattern , string_replacement )

[mathiasrw]

Can we close this one?

[MarioVanDenEijnde]

Hello Mathias,

Sorry. Yes. Thanks.

Kind regards, Mario