Skip to main content
Solved

removing leading and trailing whitespaces in records

D
Data Enthusiast
  • DjBData Enthusiast
  • Data Enthusiast

Hello Team,

I am trying to create a rule for a column “account_number” in a dev table. We are using version 14.5 and “One Desktop” to create the rule.

The requirement is to create a rule which removes the leading and trailing whitespaces from the records of the column. I tried defining below expressions in the Column Assigner step of the plan. The final step of the plan is the JDBC Writer step.

  1. trim(account_number)
  2. squeezeSpaces(account_number)
  3. trimRight(account_number)

Once I make the changes, I save it, run the plan and the plan executes successfully. BUT when I check the records in the database…

the whitespaces are still there in the database table records.

Can you please help me understand how to fix this? Which expression should help remove the whitespaces from the records?

 

Thanks,

Dhruba

Best answer by AKislyakov

Behavior of CHAR datatype in MS SQL is dependent on the ANSI_PADDING setting. (SET ANSI_PADDING (Transact-SQL) - SQL Server | Microsoft Learn)
By default, the setting is set to ON, and as a result all values are forcefully padded with blanks when saved to the table

Pad original value (with trailing blanks for char columns and with trailing zeros for binary columns) to the length of the column.

 

If you need to store data without trailing blanks, I suggest you to opt for the VARCHAR datatypes, which supports storage of variable-length strings.

View original
Did this topic help you find an answer to your question?

A
Ataccamer
Forum|alt.badge.img+2

Hi ​@DjB,

Can you please share what database engine do you use and what are the types of the fields you store the data.

Cansu
1 person likes this
  • Cansu
    Cansu
D
Data Enthusiast
  • DjBData Enthusiast
  • Data Enthusiast
  • April 9, 2025

Hi ​@AKislyakov ,

The data type of the column is CHAR(15) in the database and using in SQL server.

In Ataccama, the data type of the column is String. 

I even tried using the expression as trim(toString(account_number)) but was of no use.

 

 

Thanks

Cansu
1 person likes this
  • Cansu
    Cansu
A
Ataccamer
Forum|alt.badge.img+2

Behavior of CHAR datatype in MS SQL is dependent on the ANSI_PADDING setting. (SET ANSI_PADDING (Transact-SQL) - SQL Server | Microsoft Learn)
By default, the setting is set to ON, and as a result all values are forcefully padded with blanks when saved to the table

Pad original value (with trailing blanks for char columns and with trailing zeros for binary columns) to the length of the column.

 

If you need to store data without trailing blanks, I suggest you to opt for the VARCHAR datatypes, which supports storage of variable-length strings.

Cansu
1 person likes this
  • Cansu
    Cansu
D
Data Enthusiast
  • DjBData Enthusiast
  • Data Enthusiast
  • April 9, 2025

Thank you ​@AKislyakov , that helps! I will check by changing the datatype of the column in the DDL.

But just wanted to know if there is any Ataccama function which can be used in the DQ rule to convert the char to varchar.

Something like CAST or CONVERT? I am checking this option so that I don’t have to keep changing the DDL in case we face similar issues with multiple columns, in future.

 

Thanks,

A
Ataccamer
Forum|alt.badge.img+2

How are you creating tables? Is it using create table button in One Desktop? or manually via DDLs?

D
Data Enthusiast
  • DjBData Enthusiast
  • Data Enthusiast
  • April 10, 2025

We are mostly creating tables manually using the DDLs.

But we also change the datatype manually in One desktop if its a change in a single column.

Cansu
1 person likes this
  • Cansu
    Cansu
A
Ataccamer
Forum|alt.badge.img+2

Both CHAR and VARCHAR are mapped to STRING datatype in Ataccama. But Once tables are created the only way to change datatype is through DDL (ALTER TABLE) command.

Cansu
1 person likes this
  • Cansu
    Cansu

Reply


Community Terms of Use & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings