Teradata SQL - Use OREPLACE to Replace or Remove Characters

access_time 10 months ago visibility1802 comment 0

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')

info Last modified by Administrator 5 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

More from Kontext

visibility 189
thumb_up 0
access_time 5 months ago

BTEQ is a Teradata utility tool that can be used to run Teradata SQL statements incl. DDL, DML, etc. It can also be used to import data from text file into Teradata databases. It also works with XML and JSON files too. Like TPT and FASTLOAD, it can run in both batch and interactive modes. This ...

visibility 769
thumb_up 0
access_time 11 months ago

In Teradata, Common Table Expression (CTE) is supported as other databases. You can create recursive CTE or use a reference of a CTE to another CTE. However there is a slight differences compared with other databases - The referenced CTE must be present after the referencing CTE. For example, CTE ...

visibility 307
thumb_up 1
access_time 6 months ago

This page shows how to create Hive tables with storage file format as Parquet, Orc and Avro via Hive SQL (HQL). The following examples show you how to create managed tables and similar syntax can be applied to create external tables if Parquet, Orc or Avro format already exist in HDFS. Example ...