We have some input data that sometimes appears with   characters on the end. The data comes in from the source system as varchar() and our attempts to cast as decimal fail b/c of these characters. Ltrim and Rtrim don't remove the characters, so we're forced to do something like: UPDATE myTable SET myColumn = replace(myColumn,char(...
I am trying to paginate the results of an SQL query for use on a web page. The language and the database backend are PHP and SQLite. The code I'm using works something like this (page numbering starts at 0) http://example.com/table?page=0 page = request(page) per = 10 // results per page offset = page * per // take one extra record ...
I have a table with an XML column. This column is storing some values I keep for configuring my application. I created it to have a more flexible schema. I can't find a way to update this column directly from the table view in SQL Management Studio. Other (INT or Varchar for example) columns are editable. I know I can write an UPDATE sta...
I have a table similar to this: CREATE TABLE example ( id integer primary key, name char(200), parentid integer, value integer); I can use the parentid field to arrange data into a tree structure. Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields und...
Is it possible to order results in SQL Server 2005 by the relevance of a freetext match? In MySQL you can use the (roughly equivalent) MATCH function in the ORDER BY section, but I haven't found any equivalence in SQL Server. From the MySQL docs: For each row in the table, MATCH() returns a relevance value; that is, a similarity mea...
I have a database containing stock price history. I want to select most recent prices for every stock that is listed. I know PostreSQL has a DISTINCT ON statement that would suit ideally here. Table columns are name, closingPrice and date; name and date together form a unique index. The easiest (and very uneffective) way is SELECT * F...
I'm looking at improving the performance of some SQL, currently CTEs are being used and referenced multiple times in the script. Would I get improvements using a table variable instead? (Can't use a temporary table as the code is within functions). ...
SELECT notes + 'SomeText' FROM NotesTable a gives The data types nvarchar and text are incompatible in the add operator. as an error. ...
I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status. So I'm thinking: UPDATE sales SET status = 'ACTIVE' WHER...
varchar(255), varchar(256), nvarchar(255), nvarchar(256), nvarchar(max), etc? 256 seems like a nice, round, space-efficient number. But I've seen 255 used a lot. Why? What's the difference between varchar and nvarchar? ...
I'm writing some DB routines and I'm using prepared statements. My environment is PDO with PHP5. I understand prepared statements primarily provide a performance benefit, as well as some auxiliary bonuses such as not having to manually SQL-escape input data. My question is about the performance part. I have two implementations of a g...
What’s the best way to capitalise the first letter of each word in a string in SQL Server. ...
We're in the process of setting up a new framework and way of doing business for our new internal apps. Our current design dictates that all security logic should be handled by our database, and all information (and I mean all) will be going in and out of the database via stored procedures. The theory is, the data access layer requests ...
I'm using a custom-built inhouse application that generates a standard set of reports on a weekly basis. I have no access to the source code of the application, and everyone tells me there is no documentation available for the Oracle database schema. (Aargh!) I've been asked to define the specs for a variant of an existing report (e.g.,...
I need some software to explore and modify some SQLite databases. Does anything similar to SQL Server Management Studio or MySQLAdmin exist for it? ...
Edit: Solved, there was a trigger with a loop on the table (read my own answer further below). We have a simple delete statement that looks like this: DELETE FROM tablename WHERE pk = 12345 This just hangs, no timeout, no nothing. We've looked at the execution plan, and it consists of many lookups on related tables to ensure no fo...
Say I have two tables: users and groups, each of which has a name. I wish to provide a 'simple search', in which the user enters text and results contain both users and groups whose names contain the text. The results must distinguish between the two types. ...
how do you actually perform datetime operations such as adding date, finding difference, find out how many days excluding weekends in an interval? i personally started to pass some of these operations to my postgresql dbms as typically i would only need to issue one sql statement to obtain an answer, however, to do it in PHP way I would ...
I have 150+ SQL queries in separate text files that I need to analyze (just the actual SQL code, not the data results) in order to identify all column names and table names used. Preferably with the number of times each column and table makes an appearance. Writing a brand new SQL parsing program is trickier than is seems, with nested SE...
How would you go about proving that two queries are functionally equivalent, eg they will always both return the same result set. As I had a specific query in mind when I was doing this, I ended up doing as @dougman suggested, over about 10% of rows the tables concerned and comparing the results, ensuring there was no out of place res...