TRIM White Spaces IN-DB Connection - ODBC Connection

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!

Best Practices

Database Connection

Accepted answers

JBLove

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.

All comments

AbhilashR

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.

JBLove

I would suggest trying LTRIM(RTRIM(BusinessUnitCode)) or LTRIM(RTRIM("BusinessUnitCode"))

zrotuno

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

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335