How to compare an int column with a varchar column containing SQL comparison operators?
If I have a column1 containing 3315 and column2 containing ‘6287 OR 4596 OR 3315’ how can the value of the 2nd column be treated as integers and SQL comparison operators to allow WHERE column1 LIKE column2 to be true in this case?
You will need to CAST the Int field as CHAR and then do the comparison since your string column can have non-numeric characters in it.
Casting won’t help – it’ll only pick up the first comparison value. Standard SQL can’t do what you are asking. Platforms like PostgreSQL can define columns with array types where you could put multiple comparison values and use the IN operator. There’s no standard SQL support for generic execution of a string in a column expression. If you google ‘SQL eval’ you get various disappointed questioners and a handful of complicated and non-generic work-arounds. Even if you could make it work, the syntax you suggested doesn’t match any evaluation engine I’m aware of.
Depends on how exact he needs to be and what the string column will contain.
There are multiple ways to query against CHAR columns in MS SQL and others.
For one he could add a Full Text Index to the table and then use the CONTAINS function to do the query.
Thanks guys. I tried to simplify my example for this question and made an obvious mistake. My actual application can contain multiple AND/OR operators in the column which will likely be queried using WHERE EXISTS. I have not been successful building a query (or combination query/stored procedure) which can include the results from this column within a sub query to obtain the results I am looking for and I could not find a similar example online. Essentially I want to query a table to choose logic to be used within a second query of another table, but I cannot get this second query to execute because I cannot escape the varchar format of the stored query string.
Unfortunately this method is the only way to obtain the results I need. I want to be sure I have optimized as much as practical as this query/script will run thousands of times daily. I can achieve this with a gateway script, but I was unsure whether it could be accomplished in SQL alone. (MS SQL Server 2008)
[quote=“Tim”]Essentially I want to query a table to choose logic to be used within a second query of another table, but I cannot get this second query to execute because I cannot escape the varchar format of the stored query string.[/quote]Actually, using results from one query to create a where clause for another query is reasonable. However, the format you’ve shown doesn’t match any normal expression engine. If you are only using OR, after retrieving the “expression”, I would use a regular expression to replace each integer in your “expression” with the actual comparison including that number. Something like:import re
exp = system.db.runScalarQuery("Select column2 from myTable Where .....")
expandedexp = re.sub(r'([0-9]+)', r'column1=\1', exp)
results = system.db.runQuery("Select * From ... Where "+expandedexp)If you expect multiple results from the first query, you’ll have to rearrange this to either execute the second query once for each row of query 1, or combine the expressions from multiple rows into one huge where clause for query 2. The latter approach has to stay within your SQL server’s line length limit.
You say some expressions will use AND. If so, the above regular expression won’t work – you’ll need to figure out some way to transform your expression into a valid SQL fragment, or just store valid SQL directly.
Note that this isn’t any form of escaping – there is no escaping or standard SQL function that will parse your logic on a row by row basis. Your only other choice is a stored function on the SQL server that will parse and EXEC your logic one sample at a time. Then you’d use:results = system.db.runQuery("Select * From ... Where myStoredFunction(column1, column2)")