Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

TRIM White Spaces IN-DB Connection - ODBC Connection

zrotuno
6 - Meteoroid

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:

zrotuno_0-1587152541878.png

 

Error I am getting:

zrotuno_1-1587152698020.png

 

Thank you in advance for any assistance you can provide!

 

4 REPLIES 4
AbhilashR
15 - Aurora
15 - Aurora

The TRIM function should have worked. I tried recreating the same scenario at my end and it worked.

 

Source Data:

AbhilashR_0-1587163701351.png

After using TRIM function:

AbhilashR_1-1587163723599.png

Screenshot of my in-DB formula tool:

AbhilashR_2-1587163786266.png

 

Reading the error message on your screenshot it doesn't seem like the error is with TRIM function.

 

DQAUDIT
9 - Comet

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.

zrotuno
6 - Meteoroid

 I tried LTRIM(RTRIM("BusinessUnitCode")) first and it worked. Thanks everyone!!!!

atcodedog05
22 - Nova
22 - Nova

Hi @zrotuno ,

 

Please mark the response from @DQAUDIT as accepted solution if it solved your issue.

So that it is helpful for other community users to find the solution when they face the same issue.

 

Thank you for sharing this scenario. It was a great exposure.

Happy Analyzing : )

Labels