Trim Leading or/and Trailing Zeros in Teradata
insights Stats
warning Please login first to view stats information.
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')
info Last modified by Administrator 5 years ago
copyright
This page is subject to Site terms.
comment Comments
No comments yet.