Forum Academy Marketplace Showcase Pricing Features

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

@Bubble,

I want to search for a value using “Do a search for” case insensitive. Since “Do a search for” requires a value, and it seems it’s case sensitive, it’s unable to find all potential values with the same characters.

Let’s say I have a Data type with a Company Name field and Microsoft already exists. When someone else tries to add a new record with the same Company Name I want to disallow submission. So I want to ensure that all variations of case that a user types into an input field (“microsoft”, “MICROSOFT”, “MicRosoft”) is able to match the value already in the database (“Microsoft”).

2 Likes

Hey Keith,

Right there with you on this…wish it was easier than what I outlined here:

There were other requirements as well, but this shows one way to do the unique username portion.

–Ken

2 Likes

Thank you @mebeingken

It worked for me :slight_smile:

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

1 Like

@Kfawcett @mebeingken Our engineering team will review this request

7 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.

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

6 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

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

3 Likes

Has this been resolved?

Has this been already solved?

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.