It appears that storing the data in a BINARY column is an approach bound to perform poorly. The only fast way to get decent performance is to split the content of the BINARY column in multiple BIGINT columns, each containing an 8-byte substring of the original data. In my case (32 bytes) this would mean … Read more
How about split it into four parts – [MyColumn] Like ‘% doc %’ OR [MyColumn] Like ‘% doc’ OR [MyColumn] Like ‘doc %’ OR [MyColumn] = ‘doc’ Edit: An alternate approach (only for ascii chars) could be: ‘#’+[MyColumn]+’#’ like ‘%[^a-z0-9]doc[^a-z0-9]%’ (You may want to take care of any special char as well) It doesn’t look … Read more
This will bring back totals per property and type SELECT PropertyID, TYPE, SUM(Amount) FROM yourTable GROUP BY PropertyID, TYPE This will bring back only active values SELECT PropertyID, TYPE, SUM(Amount) FROM yourTable WHERE EndDate IS NULL GROUP BY PropertyID, TYPE and this will bring back totals for properties SELECT PropertyID, SUM(Amount) FROM yourTable WHERE EndDate … Read more
I was thinking you could use a regexp_like condition and use the regular expression to find any non-numerics. I hope this might help?! SELECT * FROM table_with_column_to_search WHERE REGEXP_LIKE(varchar_col_with_non_numerics, ‘[^0-9]+’);
The ideal way would be to normalize your design and split the two components of the column into two separate columns. One of type integer, one text. With the current table, you could: SELECT col FROM tbl ORDER BY (substring(col, ‘^[0-9]+’))::int — cast to integer , substring(col, ‘[^0-9_].*$’); — works as text The same substring() … Read more
This method assigns a 1 or 0 for null columns, and adds them all together. Hopefully you don’t have too many nullable columns to add up here… SELECT ((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END) + (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END) + (CASE WHEN col3 IS NULL … Read more
Well, the Include contradicts the where. Include says, “Load all tags.” The where says, “Load some tags.” When there is a contradiction between the query and Include, the query will always win. To return all tags from any item with at least one tag == text: var items = from i in db.Items.Include(“Tags”) where i.Tags.Any(t … Read more
What would be useful here would be a LIKE ANY predicate as is available in PostgreSQL SELECT * FROM tbl WHERE my_col LIKE ANY (ARRAY[‘%val1%’, ‘%val2%’, ‘%val3%’, …]) Unfortunately, that syntax is not available in Oracle. You can expand the quantified comparison predicate using OR, however: SELECT * FROM tbl WHERE my_col LIKE ‘%val1%’ OR … Read more
My guess is that you have a problem since you don’t close your select-tag after the loop. Consider separating your database-related code from the markup, it will help you to spot such mistakes easily <?php … // SQL part $sql = mysqli_query($connection, “SELECT username FROM users”); $data = $sql->fetch_all(MYSQLI_ASSOC); // HTML part ?> <tr> <td>Owner</td> … Read more
Using C# (.net 4.0) with SQLite, the SQLiteConnection class has a property LastInsertRowId that equals the Primary Integer Key of the most recently inserted (or updated) element. The rowID is returned if the table doesn’t have a primary integer key (in this case the rowID is column is automatically created). See https://www.sqlite.org/c3ref/last_insert_rowid.html for more. As … Read more