Contains and Begins With

Why do I have some varchar fields that do not have Contains and Begins With available in the query manager?

Ok, discovered my field isn’t a string as I thought it was. That would be why. So does ‘In’ do the same thing as contains?

Hello Brad,
The conditions are related to the data type of your fields.
Contains works with string.
In requires an array.

They are not the same.

So ‘In’ cannot be used the same way as ‘Contains’? Only exact matches will work in a number field?

No, they work differently. Contains expects a string to check while IN expects an array.

So basically I’m totally screwed. :frowning:

I am with you on this Brad, if varchar type then Contains and Begins with should be available.

After all they just equate to %mystring% or mystring%

I don’t follow the logic if they are not available for varchar fields and I confess I have never observed that

Can you post the data structure and the fields which do not offer those, options?

Hi Brian, it’s actually a number field. I was mistaken about it being a varchar. And it is in a table that has 20,000 records in it so I don’t know if I can change the field type now. So I am really screwed.

I don’t understand why Wappler won’t allow contains or begins with on an Int field. Even Navicat which is a leading Database tool allows it.

2 Likes

Are you sure Navicat allows it? It is a string only function?

As I said previously, contains does not exist in SQL, it is translated to ‘like %string%’
It would make no sense in an integer situation.

If you change the field type from INT to VARCHAR in Navicat it will change the INT values to a varchar representation i.e. 12345 will change to ‘12345’

What exactly are you trying to do with ‘contains’ and an int value?

1 Like

Can you give us an example data set Brad? And inform us what you are trying to do? You’re not screwed, there is always away around something like this, and usually works out for the better despite the initial frustration. Stick with it.

:wink:

1 Like

Positive, as my screenshot above shows.

Basically I have a field that is an int field and contains a four digit number. What I need is tto filter it by a variable (thats easy). The variable could only contain three or sometimes even two digits.

So If I have the values 7620, 7621, 7622, 7623 in the table if the variable is 762 it should bring up all matches that start with 762. I did the exact same thing on a older dashboard with the DMXZone extensions and it worked fine.

Navicat must do the data conversion on the fly. I use Navicat myself but a an old copy of version 8 lite as i refuse to pay their prices.
Think you are going to have to change the data type to resolve the issue as there is no way to change data type dynamical within the visual query manager

I am scared to change the data type as there is 20k records used by three current dashboards. I don’t know if that will break anything.

Surely it’s MySQL, not Navicat, which is running the query. I didn’t know this was possible, but I think Brad’s right. I just ran a query in Navicat:
SELECT * FROM stock where price LIKE '18%'
…and it returned:

image
I tried with INT and FLOAT, and, surprisingly, it works.

I found this - MySQLism: MySQL will automatically cast INT into VARCHAR/CHAR

This is non-standard SQL behaviour which I imagine is why it’s not available in Wappler. (Another use for the forthcoming custom query feature - hopefully.)

The question here is - are you sure your database design logic is not wrong then. Do you really need to have that column as an integer if you want to use LIKE with it?
As others mentioned that’s not a standard mysql feature/behavior and CAST could have performance implications with mysql especially with this number of records…

Its a twenty year old database. I doubt I can change it now without breaking things.

I don’t think there would be a problem changing the column type, but you could make a copy of the table first and test it on that.

Alternatively, if you don’t want to touch the existing column - or if it’s needed as both an INT and VARCHAR - then you could add an extra column: a virtual column, equal to the value of your INT column, but defined as VARCHAR.

I’ll just have to explain to the boss and the staff that use the dashboard that we can no longer do what we’ve been doing for years. Makes no sense.