# Does comparison operator =(equal) case sensitive?

**URL:** <https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919>\
**Category:** CrateDB\
**Created:** [November 29, 2021, 8:30pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919 "2021-11-29T20:30:09Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Valeriy\_Dzhura](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/valeriy_dzhura/32/433_2.png) [@Valeriy\_Dzhura](https://community.cratedb.com/u/Valeriy_Dzhura)\
**Post date:** [November 29, 2021, 8:30pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/1 "2021-11-29T20:30:09Z")

</div>

Hello guys,

After reading [this topic](https://crate.io/docs/crate/reference/en/4.6/general/dql/selects.html?highlight=selecting%20data#where-clause) I have a question, does comparison operator `=(equal)` case sensitive for varchar or text values? And can we change this behavior for table/query/server.

I know that I can use ILIKE operator like solution, but I interest behavior like in MSSQL where operator `=(equal)` is case insensitive but can be case sensitive if we will change database/table collation i.e. we can change this behavior but could we do the same in the CrateDB?

Thanks

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [November 30, 2021, 6:10pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/2 "2021-11-30T18:10:17Z")

</div>

HI @Valeriy_Dzhura

It is case sensitive yes. I am not aware of any table or general settings on cluster level.

- `ILIKE`
- using `LOWER()`
- use a fulltext index with e.g. ‘simple’ analyze

```sql
CREATE TABLE tab (
  txt TEXT INDEX using FULLTEXT with (type='simple')
 );

INSERT INTO tab VALUES ('Hello');

SELECT txt FROM tab 
WHERE txt = 'hello'
--> 'Hello'

```

---

<div class="post-metadata">

**Author:** ![Valeriy\_Dzhura](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/valeriy_dzhura/32/433_2.png) [@Valeriy\_Dzhura](https://community.cratedb.com/u/Valeriy_Dzhura)\
**Post date:** [November 30, 2021, 10:53pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/3 "2021-11-30T22:53:26Z")

</div>

Hello again,

I used next analyzer for my field:

```auto
create ANALYZER myAnalyzer(
  TOKENIZER CustomTokenizer with (type='ngram', min_gram=2, max_gram=2, token_chars=['letter']),
  TOKEN_FILTERS (my_synonyms WITH (type='synonym', synonyms_path='synonyms.txt'), lowercase, kstem));

```

and

```auto
CREATE TABLE IF NOT EXISTS "x"."x" (
"name" TEXT INDEX USING FULLTEXT WITH (analyzer = 'myAnalyzer')
)

```

And as you can see I used `lowercase` in `TOKEN_FILTERS`, but when I compare my field with this analyzer using operator `=(equal)` it is still case sensitive. Looks like `lowercase` not work for this field or maybe I do something wrong?

For example, these queries returns different results but should be the same:

```auto
select * from x.x where name = 'John'
select * from x.x where name = 'john'

```

Thanks!

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [December 1, 2021, 4:58pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/4 "2021-12-01T16:58:10Z")

</div>

The `lowercase` token filter lowercases the entries in the index. So you would need to search for the lower case

```sql
select * from x.x where name = 'john'

```

or use `lower()` in the comparison

```sql
select * from x.x where name = lower('John')

```

if you need to have both options you could create a 2nd index.

---

<div class="post-metadata">

**Author:** ![Valeriy\_Dzhura](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/valeriy_dzhura/32/433_2.png) [@Valeriy\_Dzhura](https://community.cratedb.com/u/Valeriy_Dzhura)\
**Post date:** [December 1, 2021, 5:43pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/5 "2021-12-01T17:43:18Z")

</div>

Can you show me please an example how I can use two indexes for 1 field in my test case:

```auto
create ANALYZER myAnalyzer(
  TOKENIZER CustomTokenizer with (type='ngram', min_gram=2, max_gram=2, token_chars=['letter']),
  TOKEN_FILTERS (my_synonyms WITH (type='synonym', synonyms_path='synonyms.txt'), lowercase, kstem));

CREATE TABLE IF NOT EXISTS "x"."x" (
"name" TEXT INDEX USING FULLTEXT WITH (analyzer = 'myAnalyzer')
)

```

Thanks!

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [December 1, 2021, 8:38pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/6 "2021-12-01T20:38:34Z")

</div>

> [@Valeriy\_Dzhura](#):
>
> ```auto
> CREATE TABLE IF NOT EXISTS "x"."x" (
> "name" TEXT INDEX USING FULLTEXT WITH (analyzer = 'myAnalyzer')
> )
> 
> ```

e.g. like that:

```sql
CREATE TABLE IF NOT EXISTS "x"."x" (
"name" TEXT INDEX using plain,
INDEX "name_ft" USING FULLTEXT WITH (analyzer = 'myAnalyzer')
)

```

also see [Fulltext indices — CrateDB: Reference](https://crate.io/docs/crate/reference/en/4.6/general/ddl/fulltext-indices.html)

```sql
-- exact match
select name from x.x where name = 'John'

-- using full_text index
select name from x.x where name_ft = 'john'

```

---

<div class="post-metadata">

**Author:** ![Valeriy\_Dzhura](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/valeriy_dzhura/32/433_2.png) [@Valeriy\_Dzhura](https://community.cratedb.com/u/Valeriy_Dzhura)\
**Post date:** [December 1, 2021, 9:47pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/7 "2021-12-01T21:47:04Z")

</div>

Not sure that I understand you right, but why when I use `lowercase` in `TOKEN_FILTERS` my queries works wrong, for example you can see next statement:

```auto
create ANALYZER myAnalyzer(
  TOKENIZER CustomTokenizer with (type='ngram', min_gram=2, max_gram=2, token_chars=['letter']),
  TOKEN_FILTERS (my_synonyms WITH (type='synonym', synonyms_path='synonyms.txt'), lowercase, kstem));

create table if not exists x.x(
  "name" text index using fulltext with (analyzer = 'myAnalyzer'),
  "test" varchar(8)
);

insert into x.x(name, test)
values('John','John'),('JOHN','JOHN'),('jOhn','jOhn'),('john','john');

select _score, name from x.x where name = 'john' limit 100; -- NO HIT
select _score, name from x.x where name = 'John' limit 100; -- NO HIT
select _score, name from x.x where name = 'JOHN' limit 100; -- NO HIT

```

In each select returns `NO HIT` but as you can see we have this records in the table and in each select **I should get all four rows**.

According to this:

> The `lowercase` token filter lowercases the entries in the index. So you would need to search for the lower case

My first query `select _score, name from x.x where name = 'john' limit 100; -- NO HIT` should return 1 row, but nothing. This is fully misconfused me.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [December 1, 2021, 10:02pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/8 "2021-12-01T22:02:24Z")

</div>

> [@Valeriy\_Dzhura](#):
>
> `letter`

Ah … I overlooked that you used a ngram tokenizer.  
Then you should use a `MATCH` instead [Fulltext search — CrateDB: Reference](https://crate.io/docs/crate/reference/en/4.6/general/dql/fulltext.html#match-predicate)

with

```sql
CREATE TABLE IF NOT EXISTS "x"."x" (
"name" TEXT INDEX using plain,
   INDEX "name_ft" USING FULLTEXT WITH (analyzer = 'myAnalyzer'),
"test" varchar(8)
)

```

you could do

```sql
select _score, name from x.x where name = 'john' limit 100; 

```

or

```sql
select _score, name from x.x where MATCH(name_ft, 'john') limit 100; 

```

---

<div class="post-metadata">

**Author:** ![Valeriy\_Dzhura](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/valeriy_dzhura/32/433_2.png) [@Valeriy\_Dzhura](https://community.cratedb.com/u/Valeriy_Dzhura)\
**Post date:** [December 1, 2021, 11:51pm UTC](https://community.cratedb.com/t/does-comparison-operator-equal-case-sensitive/919/9 "2021-12-01T23:51:56Z")

</div>

Thank you, but I still not understand how lowercase works and why it works like this. According to this:

> The `lowercase` token filter lowercases the entries in the index. So you would need to search for the lower case

In index it will be stored in lowercase but when I will use WHERE condition we will not look into index when will use ` =(equal)` operator. ` =(equal)` operator will look only in fields values, not in index, right? Or is it only for `fulltext` search?