Contains and Begins With

Wappler General
#21

what about duplicating the column as a varchar and querying the new column while leaving the old column unchanged?

#22

Is there a way to duplicate an entire column and have all new entries duplicated as well? Problem is I have three different dashboards that share that same data. New data gets added every day.

#23

Yes, like this (in Navicat):

image

I have an existing price column. Iā€™ve created a virtual column (which will take no extra space - itā€™s calculated on the fly) with ā€˜priceā€™ as the expression and defined as VARCHAR - named ā€˜price_textā€™. Having created this column, you will have a new, text version of the price column. It would be a duplicate and will keep in sync with the main price column.

Virtual columns can also be stored - so they do take space. Iā€™m not sure which you will need in this case.

1 Like
#24

Strange, I have the latest version of Navicat but donā€™t have the virtual option? Does it need to be turned on somewhere?

#25

I donā€™t think so - but the problem could be that youā€™re using an older version of MySQL. You need version 5.7. I should have mentioned that.

#26

Yeah, that could be it. Mine is 5.6.14 ā€¦ not having a good day. lol

#27

Thatā€™s a pity. You could create a new column for you VARCHAR version and update it to have the same values as the INT number column. Then create a trigger to set the value of this new column to the INT column - on both insert and update. I think this should produce the same result.

#28

The query builder wonā€™t allow it, it is very strict. You can edit the json in the action file to change it to begins with. Just select equal first, then edit the file and change the equal operator in begins_with. Or choose a string column and use the begins with condition there and change the column name afterwards in the action file.

#29

Iā€™m going to lose this battle but anyways :wink:

Why not just allow it in the query builder if it works?

#30

I suspect because it doesn work in all platforms or indeed all versions of mysql

Patrickā€™s solution, assuming it works, doesnā€™t seem a bad workaround

#31

I know with MSSQL you have to CAST the value first to the correct type or you will get an error. MYSQL seems to be less strict and does it for you. Other databases I donā€™t know, but according to the standard it should give an error.