Trim Leading or/and Trailing Zeros in Teradata

event 2020-07-26 visibility 14,883 comment 0 insights
more_vert
insights Stats
Raymond Raymond Code Snippets & Tips

Code snippets and tips for various programming languages/frameworks. All code examples are under MIT or Apache 2.0 license unless specified otherwise. 

In SQL Server, there are functions like TRIM, LTRIM, RTRIM to remove characters from a string. In Teradata, the equivalent is TRIM function.

Trim from leading zeros

The following code snippets remove leading zeros from the string literal.

select trim(leading '0' from '00012345000')

Result:

Trim(LEADING '0' FROM '00012345000')

1 12345000

Trim from trailing zeros

Similarly, TRIM function can also be used to remove trailing zeros:
select trim(trailing '0' from '00012345000')

Result:

Trim(TRAILING '0' FROM '00012345000')

1 00012345

Trim both leading and trailing zeros

select trim(both '0' from '00012345000')
Result:
Trim(BOTH '0' FROM '00012345000')
1 12345

Trim a column in a view or table

select trim(leading '0' from column_name) from table_name
select trim(trailing '0' from column_name) from table_name
select trim(both '0' from column_name) from table_name

Trim other characters

You can use TRIM function to trim all other characters by replacing '0' to the character you want to trim. 

select trim(leading 'A' from 'AAA2041145')
More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts