Partial string matching requires character operands

Database
Enthusiast

Partial string matching requires character operands

Hi all,

I've got an interesting issue going on with Teradata. Suppose I have a table tbl with columns col1, col2, col3 with col1 being the primary key.

I can run 

Select * from tbl where col2 like '%abc%'

However, if I attempt to run the following:

Select * from tbl where col1 is like '123%'

I receive the error: "SELECT Failed. 3544: Partial string matching requires character operands."

After much googling and snooping, I can't seem to find a root cause or resolution. Is this a Teradata "feature" which denies one the ability to use string matching against a primary key?

Thank you

4 REPLIES
Senior Apprentice

Re: Partial string matching requires character operands

#1: There's no "is like"

#2: Just read the error message: "requires character operands" means "col1" is not a CHAR or VARCHAR, but probably a numeric column.

Re: Partial string matching requires character operands

Hi,

i have a question similar to above. im testin a col

source col : x data type : decimal (38,0)

target col : y data type: varchar(1000)

transfomation rule: filter non printable characters(/n,/r,/t). remove leading and trailing filler.

iff length exceeds 1000, right trim to 1000.

can u pls help me writing query

Senior Apprentice

Re: Partial string matching requires character operands

There's abolutely no need to test if a DECIMAL contains anything else but a valid value, and which decimal has more than 1000 digits?

Re: Partial string matching requires character operands

Thanks Dneoth.

sorry  above i mentioned data type of x as decimal(38,0).but it is varchar(1000) too.

i tried writing the query to filter non printable characters but it is not working i mean it is not filtering.

rule:  filter non printable characters(/n,/r,/t).remove leading and trailing filler.