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

Need help Database
1

@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”).

image
image863×302 29.6 KB

5 Likes
2

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

4 Likes
3

Thank you @mebeingken

It worked for me :slight_smile:

4

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

2 Likes
5

@Kfawcett @mebeingken Our engineering team will review this request

8 Likes
6

Thanks @neerja!

7

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
8

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
9

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
10

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
11

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.

search case insensitive workaround
search case insensitive workaround902×512 70.3 KB

12

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

1 Like
13

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

6 Likes
14

Has this been resolved?

1 Like
15

Has this been already solved?

1 Like
17

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
18

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:

forum
forum701×373 16.6 KB

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

6 Likes
19

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
20

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
21

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