I have a table, contacts, that has a dozen columns or so. I want to identify each row in which at least one column contains a regular expression. I'm wondering if it's possible to do something like this: select * from contacts where * like '%re%'; instead of this: select * from contacts where last_name like '%re%' or first_name like ...
Are there reasons for not storing boolean values in SQL as bit data types without NULL? I see them often stored as integers without constraints to limit values to 0 and 1, and as strings with things like T/F, True/False, yes/no, etc., again without constraints. Isn't it better to store them as bits and not have to worry about additiona...
Okay, I've got what is probably a very easy question for you Sql gurus out there... Given a boolean of some kind, T, I want to populate a temp table with data set A, if T is true, or data set B, if T is false. I thought this would be how to do it: DECLARE @foo INT SET @foo = null IF (@foo is null) BEGIN SELECT 'foo was nu...
Hello, I am trying to find out when the last insert/update was done to a specific table in our sql 2005 db. The data does not have a timestamp, so I can not tell that way. Are there any dmv out there that would assist me in this? Thanks, hp Duplicate: http://stackoverflow.com/questions/301060/how-to-find-recent-sql-update-operations-a...
Duplicate: http://stackoverflow.com/questions/163434/are-nulls-in-a-relational-database-okay I dodged a heated debate concerning nulls in the database today. My opinion is that null is an excellent indicator of unspecified values. Every one else in the team, that has an opinion, thinks zero and empty strings are the way to go. Ar...
Hello, I have a table which contains events for change the state of a maintenance service. This is a sample of data: id date event 234 2009-04-22 10:00:00 Service Request 234 2009-04-22 10:02:00 Service Approbation 234 2009-04-22 10:03:00 Service Asignation 234 2009-04-22 10:40:00 Service Fulfilled ... In a report, i need to show tim...
I'm working on a PostgreSQL 8.1 SQL script which needs to delete a large number of rows from a table. Let's say the table I need to delete from is Employees (~260K rows). It has primary key named id. The rows I need to delete from this table are stored in a separate temporary table called EmployeesToDelete (~10K records) with a foreig...
I'm working on a database driven web-application (ASP.NET, SQL 2008), which receives structured XML data from various sources. The data resembles a set, and often needs 'cleanup', so it is passed through the database as XML, and turned into a resultset for display. I'd like to capture the produced 'clean' results, and send them to an a...
I've seen this, so I know how to create a pivot table with a dynamically generated set of fields. My problem now is that I'd like to get the results into a temporary table. I know that in order to get the result set into a temp table from an EXEC statement you need to predefine the temp table. In the case of a dynamically generated piv...
We use grep, cut, sort, uniq, and join at the command line all the time to do data analysis. They work great, although there are shortcomings. For example, you have to give column numbers to each tool. We often have wide files (many columns) and a column header that gives column names. In fact, our files look a lot like SQL tables. ...
is there a way to create a select statement that would retrieve data from multiple database in postgre? i was thinking it would be something like this: select * from dbname1.table1, dbname2.table2 where dbname1.table1.column1 = dbname2.table2.column1 ...
I am executing this query SELECT voterfile_county.Name,voterfile_precienct.PREC_ID, voterfile_precienct.Name ,COUNT((SELECT voterfile_voter.ID FROM voterfile_voter JOIN voterfile_household WHERE voterfile_voter.House_ID = voterfile_household.ID and voterfile_household.Precnum = voterfile_precienct.PREC_ID)) as Voter...
I'm trying to understand how ActiveRecord deals with associations that are more complex than simple has_many, belongs_to, and so on. As an example, consider an application for recording music gigs. Each Gig has a Band, which has a Genre. Each Gig also has a Venue, which has a Region. In the rough notation of MS Access (which I'm sudd...
I have a database like where: Table foo has columns id and name Table bar has columns id and foo_id I have an incoming HTTP query with a foo.name, I'd like to insert a row into bar with bar.foo_id set appropriately. So, for example: > SELECT * FROM foo; id name ------ ------- 1 "Andrey" (1 row) > SELECT * FROM bar; (0 rows)...
I've got the next problem up from this one: http://stackoverflow.com/questions/143552/comparing-date-ranges The solution to comparing two ranges is the query: SELECT * FROM periods WHERE NOT (range_start > @check_period_end OR range_end < @check_period_start) I have the added problem. I am allowing people to enter a ran...
What I'm trying to do is provide a generic search capability on a table. So the user is presented with all data in a table, they enter some text to filter on, and voila, the table is now filtered on all results that match that entry. I have this working with a single field: public ActionResult Index(string user_name) { var dataCon...
I have a query that takes input from the end user and compares it to 2 columns individually, and the two columns concatenated. SELECT f_name, l_name, (f_name + ' ' + l_name) AS full_name FROM users_table WHERE f_name = user-input OR l_name = user-input OR 'full_name' = user-input Excuse the massive syntax fail, but I assure you ...
I need to return two fields from a database concatenated as 'field1-field2'. The second field is an int, but needs to be returned as a fixed length of 5 with leading 0's. The method i'm using is: SELECT Field1 + '-' + RIGHT('0000' + CAST(Field2 AS varchar),5) FROM ... Is there a more efficient way to do this? ...
I have a working search query with the new Text Search functions in DB2 9.5.2: select * from prevue.mysearchabletable mst where contains(mst.searchabletext, 'porsche') = 1; However, this only tells me which ones match, as well as what the score is (score function has been omitted for brevity). Is there a way to use the text search in...
Hi I have a C# code which does lot of insert statements in a batch. While executing these statements, I got "String or binary data would be truncated" error and transaction roledback. To find out the which insert statement caused this, I need to insert one by one in the SQLServer until I hit the error. Is there clever way to findout ...