Databases

Regular Expressions in SQL

: Category: Databases; 09 January 2014; 09 January 2014; Hits: 20415; Rating:

( 722 Ratings )

What?
Practice makes perfect. Or in my case, any practice is a start. This article serves as a quick note on how to use regular expressions within SQL statements:

How?
For the following examples, I am pretending to select rows from a table called `STUDENTS`.

: Category: Databases; 24 July 2013; 24 July 2013; Hits: 14252; Rating:

( 615 Ratings )

What?
This is an article to remind me how to modify a column in a database table the old fashioned way (as in stop making me use GUI interfaces so poorly programmed when even I've made better DBMS tools).

All SQL

-- Add a column to an existing table (giving it datatype char(2) and allowing NULL)
ALTER TABLE myTable ADD myColumn CHAR(2) NULL

-- Delete a column
ALTER TABLE myTable DROP COLUMN myColumn 

-- Reorder a column
ALTER TABLE myTable MODIFY COLUMN misplacedColumn AFTER otherColumn;

: Category: Databases; 22 July 2013; 22 July 2013; Hits: 27062; Rating:

( 116 Ratings )

What?
Just a quick note to myself on how to reorder columns as I was having difficulty using a phpMyAdmin interface to do this.

How?
Taken from the best forum for programming Qs&As: http://stackoverflow.com/questions/4095481/easy-way-to-re-order-columns

Method: phpMyAdmin
So in the phpMyAdmin interface, apparently, you can drag the columns by clicking and holding on the headers when viewing a table structure... Though maybe this is derived from a template and depending on if you are using MS Internet Explorer...

Can't get it working? I use whatever is most useful and Google's Chrome is the fastest browser I have. Here are some ways to do this:

: Category: Databases; 08 March 2013; 08 March 2013; Hits: 25894; Rating:

( 174 Ratings )

What?
This is an article on how to do a restore from backup on a database but when you get the error: "... database is in use".

-- SQL Server 2005
EXEC SP_WHO          // details on who is logged in
GO

-- SQL Server 2008
EXEC SP_WHO2         // even more details
GO

-- Run as database owner to see ALL connected processes as well.

Why?
For every DBA this is a doddle and doesn't warrant its own article but for those of us who merely use SQL Server Management Studio (SSMS) and Microsoft's SQL Server 2008 R2 for development purposes, the once-in-a-blue-moon restore from backup process is quickly forgotten.

How?
You need to set the database to single-user mode.

: Category: Databases; 31 January 2013; 31 January 2013; Hits: 27123; Rating:

( 920 Ratings )

Some memory aids for me so that I don't have to keep looking them up:

: Category: Databases; 05 November 2012; 05 November 2012; Hits: 29340; Rating:

( 497 Ratings )

What?
We have a datawarehouse and we want to be able to count all the records in any table of the database that match on a particular warehouse load. This is a column value where the column is called "WarehouseLoadKey" and the value we want to search on is "3" (the 3rd incremental load).

How?
The below stored procedure can be reduced to just a script as long as you declare and set the parameters after the BEGIN and extract the script from BEGIN to END (excluding the words BEGIN and END - avoids the need to create a stored procedure and saving it on a database):

: Category: Databases; 26 October 2012; 26 October 2012; Hits: 13503; Rating:

( 217 Ratings )

What?
This is a quick note to show you how to convert a given comma delimited string into a database table:

Given:   "Title,Forenames,Surname"

Return:
  ID     Value
  ------ ----------------
  1      Title
  2      Forenames
  3      Surname

Note the below example omits the ID column and just leaves VALUE.

Why?
Do we need a reason?

How?

: Category: Databases; 11 October 2012; 11 October 2012; Hits: 76674; Rating:

( 923 Ratings )

Why?
Data Consistency. I was tasked with finding variations of our default values. End-users were complaining that some default values get listed twice because their system was case-sensitive despite the collation of the SQL Server being case-insensitive.

What?
End-users said that they could see the options "Data Not Yet Available" as well as "Data not yet available". These are obviously the same values, so I should go through the database and change all variations to be typed the same way in the same case.

Wouldn't it be nice to search through your entire database for similar sounding values (so not just case), and in this example, find typos as well, eg. "Daat ont Ety Aviable".

How?