Teradata SQL LIKE: Contains, Starts With, Ends With Functions

access_time 7 months ago visibility697 comment 0

This page shows how to use LIKE in Teradata to check whether a string column contains, starts with or ends with certain characters. 

All the code snippets are using string literal and you can replace them with your table/view column name if you want to apply them in your queries. 

Starts with a string

We can use % to match a 

select case when 'Kontext.tech' LIKE 'Kontext%' then 1 else 0 end

The above query returns 1 as the string literal starts with Kontext.

Ends with a string

select case when 'Kontext.tech' LIKE '%tech' then 1 else 0 end

The above query returns 1 too as the string literal does end with tech.

Contains a string

select case when 'Kontext.tech' LIKE '%text%' then 1 else 0 end

This query also returns 1 as the string literal has that sub string inside it.

You can also use other functions like INDEX or POSITION.

INDEX function

select case when INDEX('Kontext.tech', 'text')>0 then 1 else 0 end

The function will return 0 if the search string doesn't exist.

POSITION function

sel position('text' in 'Kontext.tech' )

The function will return 0 if the search string doesn't exist.

info Last modified by Administrator 6 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 13952
thumb_up 0
access_time 6 years ago

Infosphere DataStage is adopted as ETL (Extract, Transform, Load) tool in many Teradata based data warehousing projects. With the Teradata ODBC and .NET data providers, you can also use the BI tools from Microsoft, i.e. SSIS. In my previous post, I demonstrated how to install Teradata Toolkit ...

visibility 1156
thumb_up 0
access_time 7 months ago

This page shows how to trim or remove leading or/and trailing zeros using Teradata SQL.

visibility 10
thumb_up 0
access_time 30 days ago

There are two NULLs related Teradata extension to ANSI SQL functions - NULLIFZERO and ZEROIFNULL. This function converts zero to NULL and it is commonly used to avoid error like divide by zeros. SELECT 100/NULLIFZERO(0); Above query returns NULL. And the following query will get one error ...