Hello,
I am trying to trim a string field IN-DB to improve workflow processing time on my canvas however I cannot find a trim function that clears leading and trailing white spaces that works IN-DB. Has there been a solution posted? I cannot find one directly.
Problem I am trying to solve IN-DB:
Error I am getting:
Thank you in advance for any assistance you can provide!
Solved! Go to Solution.
The TRIM function should have worked. I tried recreating the same scenario at my end and it worked.
Source Data:
After using TRIM function:
Screenshot of my in-DB formula tool:
Reading the error message on your screenshot it doesn't seem like the error is with TRIM function.
Generally, the "Error Opening" message for me has always pointed to a syntax issue. This could just be a SQL dialect difference. For example, when I query on a Netezza or Teradata based DBMS environments I use TRIM(COLUMN_NM). However, on SQL Server, at least in the past, TRIM(COLUMN_NM) didn't work and instead I had to use LTRIM(RTRIM(COLUMN_NM)).
I would suggest trying LTRIM(RTRIM(BusinessUnitCode)) or LTRIM(RTRIM("BusinessUnitCode"))
If you want to rule out syntax issues, try running your original function against the environment you're querying against to see if it works. I would do it in a SQL editor tool outside of Alteryx like Toad Data Point. However, if you don't have one you could just use the Input Data tool and write a simple query in that SQL editor.
I tried LTRIM(RTRIM("BusinessUnitCode")) first and it worked. Thanks everyone!!!!