I’m using Perl’s DBI module. I prepare a statement using placeholders, then execute the query. Is it possible to print out the final query that was executed without manually escaping the parameters and dropping them into the placeholders? Thanks Answer See Tracing in DBI. The following works using…
Tag: sql
SQL LIKE condition to check for integer?
I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter “A”: That’s fine for letters, but how do I list all items starting with any number? For what it’s wo…
Best way to test if a row exists in a MySQL table
I’m trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE … and check to see if the total is non-zero or is …
Can MySQL replace multiple characters?
I’m trying to replace a bunch of characters in a MySQL field. I know the REPLACE function but that only replaces one string at a time. I can’t see any appropriate functions in the manual. Can I replace or delete multiple strings at once? For example I need to replace spaces with dashes and remove …
How to detect and remove a column that contains only null values?
In my table table1 there are 6 columns Locations,a,b,c,d,e. Locations [a] [b] [c] [d] [e] [1] 10.00 Null Null 20.00 Null [2] Null 30.00 Null Null Null i need the result like …
MySQL: select the closest match?
I want to show the closest related item for a product. So say I am showing a product and the style number is SG-sfs35s. Is there a way to select whatever product’s style number is closest to that? …
How do I compare two columns for equality in SQL Server?
I have two columns that are joined together on certain criteria, but I would also like to check if two other columns are identical and then return a bit field if they are. Is there a simpler solution than using CASE WHEN? Ideally I could just use: Answer What’s wrong with CASE for this? In order to see …
SQL : BETWEEN vs =
In SQL Server 2000 and 2005: what is the difference between these two WHERE clauses? which one I should use on which scenarios? Query 1: Query 2: (Edit: the second Eventdate was originally missing, so the query was syntactically wrong) Answer They are identical: BETWEEN is a shorthand for the longer syntax in…
Why use a JOIN clause versus a WHERE condition?
I develop against Oracle databases. When I need to manually write (not use an ORM like hibernate), I use a WHERE condition instead of a JOIN. for example (this is simplistic just to illustrate the style): I learned this style from an OLD oracle DBA. I have since learned that this is not standard SQL syntax. O…
How to order by maximum of two column which can be null in MySQL?
I want to do something like : maxof(Null,1000) should be 1000, How to implement the maxof? Answer If you know that salaryplus will always be greater than salaryminus, then you can do coalesce will return the first value which is not null, or (in this example) 0, if both values are null. Otherwise, do somethin…