String Matching

General Q&A
1

This is a two-part question.

  1. I need to use “contains” in my DB query, though I either need it to NOT be case sensitive OR I need to be able to set the DB value to all lower case in the query.

  2. How do I query a “does not contain”?

Thanks.

2
  1. contains operators and other string matching queries are always case insensitive
  2. There is not a does not contain operator available, but you may find what you are looking for using the not in operator. Another option could be defining a helper field on the model you are querying so you can directly use the != operator.

You can find more information on all of the operators available in the Querying DB Objects section of the documentation.

3
  1. I saw that “contains” is always case sensitive in the docs, but the docs are not always accurate and up-to-date, which is why I asked. The second part of my question was if there is a way to make the DB value all lower case in the query, since case sensitivity is not always needed?

My situation is that I am checking CSV uploaded values against the DB values and sometimes users do not capitalize each word, though in the DB each word is capitalized… for example:

DB value = Ponderosa Pine
CSV value = ponderosa pine

These are the same tree species, but since there is no way to ignore case sensitivity I end up with duplicates in the DB and thus duplicates in dropdown lists.

  1. What I am trying to achieve with this is that we have tree diameter descriptions, such as “greater than 24 inches and less than or equal to 32 inches” and the CSV uploads will contain just a tree diameter in inches, such as “14.3” and I need to compare the CSV data with the diameter description, which I have working well. However, there is one diameter description which is “greater than 32 inches” and if I use the “contains” operator then I will get both of the descriptions I mention… which is why I would like a “does not contain” so I could query “contains 32 and does not contain 24” which would then only return one description.

This is just one case I have, though I am sure many more instances where some form of “does not contain” would be useful.

Any ideas on how to resolve these two issues?

4

There is not a way to make the DB value all lowercase in the query. Alternatively you can create a helper field that is all lowercase without extra characters, spaces, etc. to use as a lookup.

In your example, you could have a lookup_helper field that is defined as ponderosa_pine while keeping the name defined as Ponderosa Pine. When completing the lookup from the CSV, you can then complete a search like the example below:

imported_value = imported_value.replace(/\s+/g, '_').toLowerCase();
var db_lookup = DB.tree_species.first('lookup_helper = ?', imported_value);

For the tree diameter descriptions, you could find the “greater than 32 inches” object using the following:

var db_lookup = DB.tree_diameter_descriptions.first('description contains ? and description not in ?', '32', ['greater than 24 inches and less than or equal to 32 inches']);