Command "Like" not found on column with "CASESPECIFIC"

Teradata Studio
Enthusiast

Command "Like" not found on column with "CASESPECIFIC"

 

 

Hi, 

 

This is my Question:

 

I have a table with the following structure:

 

CREATE MULTISET TABLE DWH_STAGE.Catalogo,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
Codigo INTEGER NOT NULL,
Fecha_Archivo DATE FORMAT ‘YYYY/MM/DD’ NOT NULL,
Descripcion VARCHAR(250) CHARACTER SET LATIN CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( Codigo);

 

 

Executing the next script, not return rows

 

SELECT * FROM DWH_STAGE.Catalogo WHERE Nombre_Catalogo LIKE ‘%venta%’;

 

Now, I´m executing the next script, but the result is different :

 

SELECT * FROM DWH_STAGE.Catalogo WHERE Nombre_Catalogo LIKE ‘%VENTA%’

 

Return XXXX rows.

The question, is this is correct?? Not is a BUG. The 'LIKE' command is not independient (Upper/Lower case)?.

 

Thanks.

 

3 REPLIES
Junior Supporter

Re: Command "Like" not found on column with "CASESPECIFIC"

Choosing casespefic means that searches will depend no only on the letters but also on case.  like "%AAA%' requires that 3 consecutive capital 'A's must be in the string before returning.

 

Change the case if you're having a problem.  Example: upper(columnname) like '%AAA%'.

Teradata Employee

Re: Command "Like" not found on column with "CASESPECIFIC"

Also note that the connection TMODE setting can affect the results regarding case specifity. With Studio, the connection defaults the JDBC property TMODE=ANSI (vs TMODE=TERA), which is case specific. You could try changing the TMODE setting to TERA and see if results change.

Teradata Employee

Re: Command "Like" not found on column with "CASESPECIFIC"


garrett wrote:

Change the case if you're having a problem.  Example: upper(columnname) like '%AAA%'.


It is more efficient to cast both sides of the LIKE predicate to be not casespecific.

 

Example: columnname (not casespecific) like '%AAA%' (not casespecific)