Greetings @Phani1 ,

This is an expected behaviour and follows ANSI rules. ANSI SQL requires operands of comparison operators (including LIKE) to have compatible character types and collations.

Since implicit conversion is not applied for `LIKE`, the query fails with a datatype mismatch error.

Below are two alternative approaches that can be used to address this limitation based on my experience.

Option 1 — View Wrapper (Recommended for End Users & BI Tools)

A compatibility view can be created by casting the collated column to a standard `STRING` datatype. End users and BI tools can then query the view without needing to handle collation explicitly.

Ex:

CREATE VIEW test_catalog_ph.bronze.my_table_cs_new2_vw AS

SELECT

    id,

    CAST(name AS STRING) AS name

FROM test_catalog_ph.bronze.my_table_cs_new2;

Usage:

SELECT *

FROM test_catalog_ph.bronze.my_table_cs_new2_vw

WHERE name LIKE 'Test';

Advantages:

* No changes required in user queries.

* Fully compatible with BI tools such as Power BI or Tableau.

* Hides collation complexity from end users.

* Centralized solution applied once at the view layer.

Option 2 — Helper Scalar Function

A reusable SQL function can be created to encapsulate the casting logic required for pattern matching.

Example Function

CREATE FUNCTION ci_like(col STRING, pattern STRING)

RETURNS BOOLEAN

RETURN CAST(col AS STRING) LIKE pattern;

Usage:

SELECT *

FROM test_catalog_ph.bronze.my_table_cs_new2

WHERE ci_like(name, 'Test');

Advantages:

* Standardizes pattern matching logic.

* Avoids repeated casting expressions in queries.

* Provides a cleaner and reusable workaround.

NOTE: Queries must use the helper function instead of the native `LIKE` operator.

Option 3: Alter to String if there is an option.

Alter the column data type to string if your use case does not need it to be collate.

Since automatic implicit conversion is not currently supported for `LIKE` operations on collated columns, the above approaches can be used as a workaround.

Hope this helps! 

Saicharan