# The LIKE/ILIKE keyword in CrateDB does not work when the target string contains newline (\\n) or tab (\\t) characters. This causes pattern matching to fail for multi-line or formatted text values

**URL:** <https://community.cratedb.com/t/the-like-ilike-keyword-in-cratedb-does-not-work-when-the-target-string-contains-newline-n-or-tab-t-characters-this-causes-pattern-matching-to-fail-for-multi-line-or-formatted-text-values/2089>\
**Category:** Community\
**Tags:** sql\
**Created:** [January 7, 2026, 2:19pm UTC](https://community.cratedb.com/t/the-like-ilike-keyword-in-cratedb-does-not-work-when-the-target-string-contains-newline-n-or-tab-t-characters-this-causes-pattern-matching-to-fail-for-multi-line-or-formatted-text-values/2089 "2026-01-07T14:19:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![anujjaiswar](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/anujjaiswar/32/1291_2.png) [@anujjaiswar](https://community.cratedb.com/u/anujjaiswar)\
**Post date:** [January 7, 2026, 2:19pm UTC](https://community.cratedb.com/t/the-like-ilike-keyword-in-cratedb-does-not-work-when-the-target-string-contains-newline-n-or-tab-t-characters-this-causes-pattern-matching-to-fail-for-multi-line-or-formatted-text-values/2089/1 "2026-01-07T14:19:59Z")

</div>

For example, the query below returns an empty result:  
SELECT \* FROM table  
WHERE message ILIKE ‘%A user account was%’;

But the actual `message` column value is:

```auto
A user account was deleted
accountid : amit

```

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [January 7, 2026, 2:31pm UTC](https://community.cratedb.com/t/the-like-ilike-keyword-in-cratedb-does-not-work-when-the-target-string-contains-newline-n-or-tab-t-characters-this-causes-pattern-matching-to-fail-for-multi-line-or-formatted-text-values/2089/2 "2026-01-07T14:31:38Z")

</div>

Hello there, what version of CrateDB are you running? I cannot reproduce.

##### Query ran at 2026-01-07T14:31:00.662Z on CrateDB 6.0.3

```auto
SELECT
  *
FROM
  t
WHERE
  a ILIKE '%line%';

```

##### 1 record(s) returned in 0.0100s, showing 1 row(s).

| a |
| --- |
| “It’s line one\nIt’s line two” |

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [January 7, 2026, 2:46pm UTC](https://community.cratedb.com/t/the-like-ilike-keyword-in-cratedb-does-not-work-when-the-target-string-contains-newline-n-or-tab-t-characters-this-causes-pattern-matching-to-fail-for-multi-line-or-formatted-text-values/2089/3 "2026-01-07T14:46:34Z")

</div>

It can be reproduced like this on CrateDB 6.1.1:

```sql
CREATE TABLE q1 (a TEXT);

INSERT INTO q1 VALUES (
$$
A user account was deleted
accountid : amit
$$
);

SELECT *
FROM q1
WHERE a ILIKE '%A user account was%';
-- returns no result

```

The same process on PostgreSQL does return a result:

```sql
postgres=# CREATE TABLE q1 (a TEXT);
CREATE TABLE
postgres=# INSERT INTO q1 VALUES (
$$
A user account was deleted
accountid : amit
$$
);
INSERT 0 1
postgres=# SELECT * FROM q1;
             a              
----------------------------
                           +
 A user account was deleted+
 accountid : amit +
 
(1 row)

postgres=# SELECT *
FROM q1
WHERE a ILIKE '%A user account was%';
             a              
----------------------------
                           +
 A user account was deleted+
 accountid : amit +
 
(1 row)

```

That makes it look like a bug to me 🙂. @anujjaiswar, since you discovered it, would you like to report it on [GitHub](https://github.com/crate/crate/issues) so that it can be addressed by the development team?

Thanks!

---

<div class="post-metadata">

**Author:** ![anujjaiswar](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/anujjaiswar/32/1291_2.png) [@anujjaiswar](https://community.cratedb.com/u/anujjaiswar)\
**Post date:** [January 8, 2026, 6:39am UTC](https://community.cratedb.com/t/the-like-ilike-keyword-in-cratedb-does-not-work-when-the-target-string-contains-newline-n-or-tab-t-characters-this-causes-pattern-matching-to-fail-for-multi-line-or-formatted-text-values/2089/4 "2026-01-08T06:39:04Z")

</div>

Yes, I have reported this issue, and the reference details are provided below.

> <https://github.com/crate/crate/issues/18883>
>
> \### CrateDB version
> 
> 6.1.1
> 
> \### CrateDB setup information
> 
> 
> 
> 
> \### Problem descri…ption
> 
> If we have a data like having newline in the string and tab character(\\t), multi-line text.
> column data :- 
> 
> \*\*A user account was deleted
> 
> accountid	:	amit\*\* 
> 
> \### Steps to Reproduce
> 
> CREATE TABLE q1 (a TEXT);
> 
> INSERT INTO q1 VALUES (
> $$
> A user account was deleted
> accountid : amit
> $$
> );
> 
> SELECT \*
> FROM q1
> WHERE a ILIKE '%A user account was%';
> \-- returns no result
> 
> \### Actual Result
> 
> giving no result.
> 
> \### Expected Result
> 
> It should give me the match string as per like operator.