Why oh why am I unable to search the DB case insensitive?

Thanks Ken! Having to create duplicate fields, just to make one lowercase, is a real pain.

2 Likes

@Kfawcett @mebeingken Our engineering team will review this request

8 Likes

Thanks @neerja!

Strangely enough, when you filter a search with “any field contains” (an input’s value for example) it does perform a case insensitive comparison. But I guess that doesn’t work for you, @Kfawcett as you only want to perform the comparison with one single field, not any field.

2 Likes

You may be unable, but Bubble actually isn’t. So talking about workarounds.

Data API will do case insensitive search for you server-side.

https://yourapp/version-test/api/1.1/obj/thingname?constraints=[
	{
	"key":"fieldname",
	"constraint_type":"text contains",
	"value": "value"          
	}
]

You could potentially expose Data API for that thing and create a privacy rule for the field you want to search. If response.count is != 0 you have a match.

{
    "response": {
        "results": [
            {
                // Results
            }
        ],
        "cursor": 0,
        "count": 1,
        "remaining": 0
    }
}

Or Bubble could just expose the option in the editor given that this is already built :slight_smile:

1 Like

Convert the input into Upper Case in a hidden input field …

The do an advanced search on the database field :uppercase = hidden field’s value

https://buildingonbubble.com/block/search-ignoring-case-1473495213720x341425763853008900

1 Like

It would be good to have a checkbox ‘Ignore case sensitivity’ when doing the search.

I also noticed the Search bar in the Editor’s Data tab is case insensitive, so I am guessing this is something they could do

16 Likes

Since it seems it’s still not straightforward to do a case insensitive search, I added a “Searchable Text” field to Type Board which is the type I’m trying to search on the front end. When creating or updating a Type Board object, I combine the Board Title field with a couple other short text fields I want to include in the search then save a single string to the Searchable Text field AND convert it to lowercase so I can run searches on that one field. I don’t like duplicating data, especially if it’s large text fields, but this seems like the easiest workaround for now. Plus it makes searching multiple text fields a bit more straightforward since you only have to search one field.

You can see the front end Search statement in the screenshot.

How can I filter with “any field contains”…?? That would be amazing

1 Like

strange that this is still an issue after this has been raised many times, many years ago.

6 Likes

Has this been resolved?

1 Like

Has this been already solved?

1 Like

I set a custom state on the input called lowercase. When the input is changed, I update it to the value of the input converted to lowercase, then when filtering I use the lowercase state instead of the input value.

3 Likes

This VERY simple… no need plugins , just do this:
I used a type PISSOAS
when you click your SAVE button, the workflow will be something like this:

Nothing else. Now you have a case insensitive search.
Hope this help you all.

6 Likes

Yeah… I’m reading this thread and still don’t understand whether yes or no I can do a search with any field contains case insensitively.

I’m searching through a library of books and would like to search for any fields, author, titles etc. without having to worry about case sensitivity.

Please help.

1 Like

Looks like:

ANY FIELD CONTAINS = is case insensitive, yes.
But it behaves like CONTAINS KEYWORD(S) → only searches for full words and not like CONTAINS for fragments.

So not really a solution in many cases (e.g. in mine).

1 Like

Any news or new ideas anybody?

I would be happy if:
a) I could simply apply :lowercase on both sides of the search constraint (data thing field + Input field) whereas it is currently only possible on the input side.
b) there is a way to define that this condition of the search should be executed case insensitive on the backend side.

Note: I can’t use the advanced filter & hidden field workaround suggested by the always inventive @NigelG as I don’t want to do it on the client side (the dataset is expected to grow too large).

1 Like

Is this still an issue?

I can only lowercase the right hand side (user input), but not the left hand side (database field). See image below

So the only workaround now is to have a duplicate database field but all-lowercase?

image

3 Likes

Yup, still an issue

1 Like

Gracias … Bueno