Trim Leading or/and Trailing Zeros in Teradata

access_time 4 months ago visibility386 comment 0

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')
info Last modified by Administrator at 2 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer teradata-utilities

visibility 51
thumb_up 0
access_time 2 months ago

Teradata FastExport is a command utility tool that can transfer large amount of data from Teradata database to a file.  One of the commonly used scenarios is to export data from a table or view to a text file and then load the export file into a different server.  Follow the following ...

local_offer spark local_offer SQL

visibility 20
thumb_up 0
access_time 8 days ago

In Spark, function to_date can be used to convert string to date. This function is available since Spark 1.5.0. SELECT to_date('2020-10-23', 'yyyy-MM-dd'); SELECT to_date('23Oct2020', 'ddMMMyyyy'); Refer to the official documentation about all the datetime patterns.  ...

About column

Code snippets and tips for various programming languages/frameworks.

rss_feed Subscribe RSS