Tag Archives: database

SQL Tricks – Remove Interior Spaces

This week, I was given a set of data to import to a database. After the import, I noticed that the source data had lots of unnecessary spaces all over the place. I can, of course get rid of leading … Continue reading

Posted in MS SQL, Transact SQL | Tagged , , , , , , , | Leave a comment

Vigenere By SQL

After that previous post, I suppose it was natural for me to wonder what it would take to apply Vigenere cypher logic using T-SQL. I knew right off that the code would look much different. Mainly, because my self-imposed rule … Continue reading

Posted in MS SQL, Transact SQL | Tagged , , , , , , , , , , , | Leave a comment

Using Tally Table to Remove Invalid XML Characters

A couple of weeks ago, I was called in to troubleshoot an error occurring in a SQL stored procedure. The procedure was selecting a variety of information out as XML data. The error occurred because on of the columns being … Continue reading

Posted in MS SQL, REGEX, Transact SQL | Tagged , , , , | Leave a comment

Using SQL to Mege Three Incomplete Data Sets

You can join data sets. LEFT JOIN, RIGHT JOIN, even OUTER APPLY. There’s nothing to learn here. Last week, I had what seemed to be a simple join of three data sets. My permutation was a lot more complicated than … Continue reading

Posted in MS SQL, Transact SQL | Tagged , , , , , , , | Leave a comment

Logical Tests Using OR in Crystal Reports

Consider these two formulas in Crystal Reports:

One would think that these two should behave the same, right? In fact, the results are different if Table.Value is null. In the first case, “if isnull({Table.Value}) gets evaluated first. Since … Continue reading

Posted in Uncategorized | Tagged , , , , , , , , , | Leave a comment

Update Across Joined Tables in MySQL

I had to do a MySQL update on records that were retrieved from a two-table join today. I shouldn’t have been surprised to find that I needed a different syntax than I know from MS SQL, where I would write, … Continue reading

Posted in MS SQL, mysql, Transact SQL | Tagged , , , , | Leave a comment

Easy Foreign Key Drop and Restore for SQL Server

From time to time, we’ve all had a database query to run that was stymied by foreign key constraints. In my case, it was a request to restore a table’s values from one copy of the system to another. We … Continue reading

Posted in Database Design, MS SQL, Transact SQL | Tagged , , , , , , | Leave a comment

Tracing MySql Transactions

Anyone who has had to troubleshoot or support systems built on a SQL Server database knows and loves the Profiler Tool, which allows you to see all of the queries submitted to a database. Web page database interaction tends to … Continue reading

Posted in Linix/Apache, mysql, php, Web Apps | Tagged , , , , , , , , | Leave a comment

Waitfor it

Suppose you are charged with system QA for a complicated system that expects dozens or hundreds of simultaneous users. The system has a user front end through either a browswer of through deployed software, and it sends transactions back and … Continue reading

Posted in MS SQL, Software QA/Testing, Transact SQL | Tagged , , , , , , , | Leave a comment

Simultaneous MySQL DB Connections in PHP

I recently added a copy function to one of the web applications that I’ve developed. In addition to letting the user copy records to a new set of records in the same system, I decided to also allow them to … Continue reading

Posted in mysql, php, Web Apps | Tagged , , | Leave a comment