Is it possible to do an SQL query with WHERE X > Y where the referenced table contains string data?

Hi,

I have my data in string tables and I am wondering if it is possible to do an SQL query on the table to look for the rows WHERE the value in a certain column is above or below a certain value, i.e.

 SELECT * FROM table WHERE bestBeforeTime < currentTime

The bestBeforeTime is in a string column and I would like to keep it that way as other code counts on all columns being string data.

I know it is possible to do a compare like this for numerical values, but is it possible to do with a string value? It does not seems to work when I change the current time into a string an then compare the values.

Thanks!

Kind regards,
Axel

Hi Axel,

Please try the following code

query(concat("SELECT * FROM table\
WHERE bestBeforeTime<",numtostring(currentTime,0,0)));

You should use numtostring() command which return a string contains a number value

Hi Sagar,

Thank you, but I cannot get it to work correctly. If I do as you outlined above the query does not find anything. Only if I change the table column into numercial format. If I put the timestring into '-signs then the query finds something, but the results are wrong as the code seems to perform a string comparison for the "greater" of the strings. Which gives weird results.

I use a for loop to solve the problem currently which works, but the query could be faster of course.

Any clue on the issue?

Axel

Hi Axel,

What is bestBeforetime?What values you want to fetch based on this query?

Hi Sagar,

The bestBeforeTime is the time in model seconds when the product gets too old. The time is stored as a string in the table and this seems to be the problem. If I change the same time into a number then the SQL query works as it should. But if the value is stored as a string then the query does not work.

Guess I will need to change the column in th etalbe into number format.

Thanks!
Axel

Hello Axel,

I haven't got a solution since you have found one. But in my search for an answer I have got to the arithmetic of boolean algebra. First of all you use the boolean algebra. In excel there exists the statement strings are greater than numerical values. That is maybe the problem you get when you compare strings with numerical values.

Jörg

When comparing strings, SQL will compare them like using a standard string compare, i.e. it will compare their alphabetical order.

You can always explicitly convert them to numbers as part of the query:

SELECT * FROM table WHERE stringtonum(bestBeforeTime) < stringtonum(currentTime)

As of 7.7 you can use any FlexSim command as part of the query.

Hi Anthony,

Great, thank you! Did not realize that I can turn both strings into numbers to get the correct comparison. Comparing them as strings obviously does not give the desired result.

Axel