Teradata SQL - Use OREPLACE to Replace or Remove Characters

event 2020-04-04 visibility 14,021 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. 

OREPLACE functions in Teradata can be used to replace or remove characters from a string. OREPACE is Teradata's extension to ASNI SQL. The usual REPLACE function is not available.

ANSI SQL REPLACE function

REPLACE function is commonly implemented in many other SQL databases such as SQL Server, MySQL, BigQuery, Oracle, etc.

The syntax is:

REPLACE(original_value, from_value, to_value)

For example, the following code snippet replaces word 'Context' with 'Kontext'.

SELECT REPLACE('Context is a great data engineering website.', 'Context', 'Kontext')

Teradata OREPLACE function

In Teradata, the equivalent function is OREPLACE and the syntax looks like this:

REPLACE(source_string, search_string, replace_string)

* replace_string is optional. This function replaces every occurrence of search_string in the source_string with the replace_string. Use this function either to replace or remove portions of a string.

When replace_string is not specified, it will remove search_string from source_string.

Example

SELECT OREPLACE('Context is a great data engineering website.', 'Context', 'Kontext')

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