Query a String with LIKE

MongoDB Realm Realm SDKs
#1

I am hoping to do a filter query with something along the lines of LIKE, but not sure how to include the question marks or wildcards.

I have a text field where the user is typing in their phone number.

I have a phone string property in the object I’m querying. I want to find the user by their phone number, but if there we ( ) or - or . included in the string then it doesn’t match.

For instance, if I have this object and state variable:

Person(name: “Greg”, phone: “415-867-5309”)
this.setState({ phone: “4158675309” })

then I want this query to include this object:

realm.objects(‘Person’).filtered(‘phone CONTAINS $0’, this.state.phone)

Is this possible?

#2

Hi @Kurt_Libby1
I would approach this problem by normalizing both sides of the search so that you can do a simple equality match. This would involve transforming all stored phone numbers into a standard format that removes “(,),-” or some known format that you choose. Then you can do the same transformation for the input that is coming in from the user text field. At that point you can query using ==, or CONTAINS directly.

If that doesn’t work for your app, you can use LIKE, but you will have to inject the wildcards into the input string. Maybe inject * between every number or some stricter match that you can come up with. If you are dealing with a large dataset, it is much more efficient to use direct matching, or even CONTAINS rather than LIKE, so that’s why I’d suggest normalizing your input if possible.

1 Like
#3

First see the classic NSPredicate Cheat Sheet It’s just a reference piece but good content.

I would suggest changing your model to force consistency. e.g. don’t store this “415-867-5309”, but store this “4158675309”. You can always format it with the dashes in the UI. That makes the queries a snap.

Inconsistent data usually ends up being a nightmare to work with so the above is suggested - but to more specifically answer the question if you have two objects stored in Realm with the following phone numbers:

111-222-3333
(111)222-3333

and you want to return them both, here’s the query

("phone LIKE %@", "*111*222*3333") //Swift but the format is the same for the LIKE

1 Like
#4

I have a more user-friendly approach. This is informed by being an Australian where we have different phone number structures to the USA, as does a lot of the rest of the world. It’s always annoying to have an app group our numbers weirdly.

Store two fields. Save a string version with whatever formatting the user wants to use.
The field used for searching and sorting is an Int which you update from the string, just pulling out the digits.

Int64 has the range 9,223,372,036,854,775,807 which is more digits than any legal phone number.

If you want to save an international flag, one cheat is to use the negative sign as the flag for local numbers (which actually matches the + used on entering an international number).

2 Likes
#5

Thanks @Andy_Dent.

This is what I ultimately went with. I do have users all over the world and none of their databases would be querying more than a few thousand records and most of them would only be hundreds at the most.

For reference in case anyone else finds this post, this is what it ended up looking like:

let filteredPhone = this.state.phone.replace(/(.{1})/g,"$1*");
const filteredPeople = realm.objects('Person').filtered('phone LIKE $0', filteredPhone);
#6

@Kurt_Libby1

For future readers - can you elaborate a bit on how your final solution answers the question in the original post? Just want to tie the two together for anyone that’s just getting started. Sometimes code is challenging to read unless the intent is stated.

#7

Yeah @Jay.

I do have people with phone numbers all over the world in all kinds of formats and some that even deal with people on opposite sides of international borders, so there ends up being extra symbols lie +, -, ( ), etc in the string.

Rather than forcing a second variable, stripping out all of that formatting for everyone, figuring out the use case in 100+ different countries, etc, I am just adding the wildcard character between every character in the string that is being searched for:

this.setState({ phone: “4158675309” });
let filteredPhone = this.state.phone.replace(/(.{1})/g,"$1*");

The .replace() function converts the string to 4*1*5*8*6*7*5*3*0*9*

Now, when the filter runs it includes Person objects that may have additional characters in their phone string.

1 Like