Replacing Specific Characters

Follow
Greg

Hi there

I am quite new to datameer and I am trying to use an SQL sheet to display data from another sheet and replace characters in a string. I have tried using this syntax:

REPLACE (string_expression, string_pattern, string_replacement)

REPLACE (COLUMN_NAME, 'INCORPORATED', 'INC')

but it returns no results - even though I know there should be results

The error log has a line that states

Unsupported number of arguments: Expected 4 but was 3

Can anybody tell me what I am doing wrong?

Thanks in advance!

0

Comments

12 comments

Date Votes
  • Alan

    Hi Greg,

    The REPLACE function requires the following:

    1st argument: The string in question you are working with.

    2nd argument: The starting position within the string where the replace should begin.

    3rd argument: The number of characters to replace.

    4th argument: The string to use for replacement.

    I believe in the example given here you would be better served with the REPLACEALL function.

    Example syntax for your given information would be:

    REPLACEALL(#Sheet!Column; 'INCORPORATED'; 'INC')

    Please give the above documentation links a read and let me know if you have any further questions.

    0
    Comment actions Permalink
  • Greg

    Thanks for your quick reply Alan
    Can't get it working though...
    I copied your example exactly

    REPLACEALL(#Sheet!Column; 'A'; 'B')

    and the first error I got was:
    Lexical error at line 5, column 12. Encountered: "#" (35), after : ""

    So I removed the # and got
    Lexical error at line 5, column 18. Encountered: "E" (69), after : "!"

    Tried just the column name and then I got
    No match found for function signature REPLACEALL(<CHARACTER>, <CHARACTER>, <CHARACTER>)

    I have a feeling I am overlooking something very obvious but I am not sure what

     

    0
    Comment actions Permalink
  • Greg

    And also got an error message about the ;  it seems it wants ,

    0
    Comment actions Permalink
  • Konsta Danyliuk

    Hello Greg.
    What Datameer version do you use?

    I've tested the formula REPLACEALL(#InitialName;"INCORPORATED";"INC") at my Datameer-7.2.7 and it seems to work. Whether the result at below picture is close to what you are looking for? If it doesn't, please provide few initial records and the result you would like to have. 

     

    0
    Comment actions Permalink
  • Greg

    Hi Konsta

    I am using version 7.2.3
    I can replicate what you provided in the formula bar, and it works.
    The sample you provided is exactly what I am after

    However, what I am trying to do is incorporate REPLACEALL or REPLACE into an SQL query on an SQL Sheet.

    Regards

     

    0
    Comment actions Permalink
  • Alan

    Hi Greg,

    Sorry, went off on a wrong direction!

    I got this working:

    SELECT REPLACE(column, 'INCORPORATED', 'INC') FROM sheet

    0
    Comment actions Permalink
  • Greg

    Thanks Alan
    Still not working for me though...

    This works as expected
    SELECT Entity_LegalName FROM GLEIF ( returns 5K rows)

    Change it to this
    SELECT REPLACE(Entity_LegalName, 'LTD', 'LTD.') FROM GLEIF

    and it returns 0 rows

    Stumped as to why...

    0
    Comment actions Permalink
  • Alan

    Hi Greg,

    Is this only happening with this one column?  Did it work replacing INCORPORATED with INC?

    I tested a similar query:

    SELECT REPLACE(words,'LTD','LTD.') FROM words_csv2

    I don't have a 7.2.3 environment handy at the moment, but I did test it on both 7.2.6 and 7.4.3 - and it worked in both for me.

    A  couple of things:

    1. At the bottom of the sheet - is it requesting you to run for full results?

    2. You could try escaping the period with a backslash as:

    SELECT REPLACE(words,'LTD','LTD\.') FROM words_csv2

    This also worked as expected in my environments.

    Lastly I'd like to note that there are 20+ fixes and improvements to SQL Sheets in the latest 7.2.9 release - so it's possible you're running into a bug or limitation on the 7.2.3 release.

    0
    Comment actions Permalink
  • Greg

    Hi Alan

    It is requesting I run at the bottom but when I do, I get an error.
    This error log includes the line

    Unsupported number of arguments: Expected 4 but was 3

    Tried on other columns but the issue persists

    If it is a bug it would seem the issue is that the front end is expecting 3 arguments for REPLACE in SQL and the back end is expecting 4 as there are for the function REPLACE 

    I will see about upgrading to the latest version.

    Thank you very much for your help

    0
    Comment actions Permalink
  • Alan

    Hi Greg,

    Can you tell me more about the data source?

    If you duplicate the sheet where the data is coming from and reference the duplicate in the REPLACE - does it help at all?

    I've done some digging and found an internal project describing this exact behavior - but it's supposedly only fixed in the upcoming 7.5 release.  I'd like to try and understand more about the conditions to see if I can reproduce the behavior.  If I can reproduce it, I may be able to determine a workaround.

    0
    Comment actions Permalink
  • Alan

    Hi Greg,

    After some further research, I found the key in which the fix was merged - this is indeed fixed in 7.2.6 and higher on the 7.2.x track, and is fixed in all 7.4 releases.

    Apologies for any inconvenience - in the mean time you can still use the regular REPLACEALL function on a standard formula sheet to do the replaces - then use that sheet as the data source for your SQL Sheet.

    0
    Comment actions Permalink
  • Greg

    Hi Alan

    I just learned today that we are upgrading to 7.4 in a few weeks.
    In the mean time your workaround using the formula bar to make the replacement and then referring to that sheet in the SQL is great.

    Thanks again for your help and perseverance.

    Regards

    Greg

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post