Hi, Does anybody know if it's possible to do a %LIKE% search against a column in a SQL Server database but get it to ignore any special characters in the column? So, for example if I have a column called "songs" and they contain the following... Black Or White No Sleep 'till Brooklyn The Ship Song Papa Don't Preach If the user...
I am trying to search questions which have a given tag. How can you fix the following problem? Tables questions | tags -------------------|----------------- question_id | tag title | question_id was_sent_at_time | My code SELECT question_id, title FROM questions WHERE question_...
Instead of using NVarchChar (max) for my field I am limiting the lengths arbitrarily. WHen does limiting these fields really make a significant difference in terms of performance? Every 100? 200? 1000 characters? ...
I have a piece of code that executes about 500,000 inserts on a database. It's now done in a loop calling PreparedStatement's executeUpdate at each iteration. Would it be faster to add all 500,000 inserts to a batch and and call executeBatch just once? ...
How can you select question_id and tags for a question when one of the tags is known? I am building a tag-search which searches questions by one tag such that the result shows the question and its tags including the one which was used in the search. I use the same tables as in this question. They are Tables questions | ...
We want to search for a string (ie. "Hello World") in all our database that has about 120 tables. We thought about doing a dump like mysql dump but it came out in a weird bak format. The search should be done in each column for each table. Is this possible with any type of script, or this is harder than it sounds to me? ...
I have a Location table in my OLTP which, while coding SSIS against it, I found a LocationCode column name to have a trailing space in it. Create Table Location ( [LocationId] INT IDENTITY (1, 1) [LocationCode ] INT ) Note that LocationCode column name has a trailing space. And yet, the following code works. SELECT LocationC...
This question is a follow up to this post: http://stackoverflow.com/questions/192220/what-is-the-most-efficientelegant-way-to-parse-a-flat-table-into-a-tree I liked the ClosureMap solution, but I have an additional problem to solve. How can you easily retrieve the path to a particular node in a tree? For example, if you look at the t...
This is a very standard newbie question, but I am looking for an expert clever way to do this with little code. (I know how to do this long hand, with procedural code and cursors, so we can skip that part of the answer.) Basically, I have a de-normalized data set for which I need to extract a normalized table in a set processing way. T...
Is it normal behaviour for a DataReader to return the rows from a query out-of-order? I'm fetching some rows from a PostgreSQL 8.3.7 database and am using ORDER BY, LIMIT and OFFSET as follows: SELECT id, name FROM tbl_foo ORDER BY name LIMIT 10 OFFSET 0; If I run this query manually the results are sorted and then the first ten rows...
Hello I have the following tables : TableA ID | SomeInt 1 55 1 66 2 77 TableB ID | OtherInt 1 ComputedBy Field 2 ComputedBy Field The computed by field needs to return the sum from tableA where TableB.ID = TableA.ID, but if I say: SELECT SUM(SomeInt) from TableA where ID = TableA.Id where the first ID would be ...
I have One main table( Say main) and another Temp Table( say copy). What I am trying to do is averages and Standard Deviation of each Stored proc( there are bunch of them with different version number as suffix and they all has to be treated same) from main table and update Temp table with averages and standard deviation for each day. Fo...
I have an event log in a SQL Server database. Essentially it records when a call was made and when that call ended at a call center (as two different records), as well as a few other details. I am trying to get an idea of how many phone lines are being used at any given time with this data. I can't think of any good way to have a SQL que...
I don't do a lot of SQL,and most of the time, I'm doing CRUD operations. Occasionally I'll get something a bit more complicated. So, this question may be a newbie question, but I'm ready. I've just been trying to figure this out for hours, and it's been no use. So, Imagine the following table structure: > | ID | Col1 | Col2 | Col3 |...
In a DB I'm designing, there's one fairly central table representing something that's been sold or is for sale. It distinguishes between personal sales (like eBay) and sales from a proper company. This means there is literally 1 or two fields which are not equally appropiate to both cases... for instance one field is only used in one cas...
I'm writing an SSRS report that will change the value of one of the fields based on the value of another field in the same row. Would the best way to do it via SQL? i.e.-Multiply FieldX (float) by -1 if the value of FieldY ends with D,DA, or 'DB', etc. I was initially looking at using a case statement, however I was getting a little s...
Let's say i have 2 tables Customer and Books. Table Books can have multiple rows that pertain to a row in Customer. Ex: customer John Doe has an id of 1000. Table Books has 2 rows with a member_id column with 1000 (John Doe). These 2 rows are identical EXCEPT one of the fields (book title) is empty. The other row has a value for a tit...
What's the simplest (and hopefully not too slow) way to calculate the median with MySQL? I've used AVG(x) for finding the mean, but I'm having a hard time finding a simple way of calculating the median. For now, I'm returning all the rows to PHP, doing a sort, and then picking the middle row, but surely there must be some simple way of d...
Using a case-then block, I need to choose how to order my SQL 2008 query, by [status] ASC, [date] DESC or just [date] DESC. I know only how to use one column: SELECT * FROM table ORDER BY CASE WHEN @flag = 0 THEN R.[date] END DESC, CASE WHEN @flag = 1 THEN R.[status] END ASC How to use both columns in second CASE? ...
I am having trouble with a ALTER TABLE command that I am trying to use on a MS Access database in a C# project. I am trying to rename a column and change the type at the same time. Here is my command: string sqlCommand = "ALTER TABLE " + tableName + " CHANGE [" + oldColName + "] [" + newColName + "] " + colType; What is wrong in...