Equivalent of Oracle PL/SQL TRIM function in SQL Server (T-SQL)

In PL/SQL, when the TRIM function is called with just the data parameter it will strip the leading and trailing spaces.   You can accomplish the same functionality in t-sql by using two t-sql functions:  LTRIM (strips leading spaces) and RTRIM (strips trailing spaces).  An example of accomplishing this would be the following:

One thing to note is that TRIM with the LEADING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the LTRIM function in t-sql;  TRIM with the TRAILING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the RTRIM function in t-sql.

You can also create a user defined function for TRIM in your database.  The following listing below will do that for you.

After running the above script you could test it by running the following:





How to combine values from multiple rows of a single column (T-SQL, Microsoft SQL Server, FOR XML PATH, CSV ).

The Problem:

I need a way to roll-up multiple rows into one row and one column.  In essence I needed to convert something that looked like this:

Original SubCategories Lisitng

Original SubCategories Listing

Into something that looks like this:

SubCategories formatted data

SubCategories formatted data

The Solution:

The solution I will show will make use of the FOR XML PATH clause and STUFF commands in T-SQL.


Will will make use of the XML functionality of SQL Server to amalgamate the series of rows into one row.  In order to create a XML string you must append the FOR XML command after any regular query.   There are four options to append to the FOR XML PATH command which will alter what the result will look like.  These options are either RAW, AUTO, EXPLICIT, or PATH.  For this solution we will use the PATH option (will explain these four option in more detail in a later post).

The following query shows a regular query which retrieves the subcategory name in an XML string

The results look like the following:


 You will notice that you get a formatted XML string without a root node.  To convert this list to a CSV string we just need to append a comma (“,”)  to the name field.

The following sql query demonstrates the XML string being converted to a CSV string:

CSV Result

CSV Result

This is getting a bit closer to the results we want, but we if you haven’t noticed the string begins with a comma which we should remove.  In order to do this we will use the t-sql STUFF function

STUFF() Function

The syntax of the t-sql STUFF function looks like the following:

To remove the first character of the string all we need to so is do something like the following:

This says, starting with the first 1 character place, replace 1 characters with nothing ''.

What does the final solution look like?

SubCategories formatted data
SubCategories formatted data



Run Transact-SQL Script Files Using sqlcmd–SQL Server T-SQL


I recently received a script which creates a database and loads data.  I was unfortunately too large for SQL Server Management Studio to process.


You can use the sqlcmd utility to run the script file. The example below shows the execution of a script file called script.sql which is located on the C drive and a connection is made to the SQL Server using integrated security.

To run the script file

  1. Open a windows command prompt window.
  2. In the command prompt window, type: sqlcmd –S SQLServerName-i C:\DatabaseLoad.sql
  3. Press ENTER.

How to delete duplicate records from your SQL Server tables – T-SQL

Below you will find two methods that can be used to remove duplicate records from you Microsoft SQL Server tables.

Method 1:

This method will only work in SQL Server 2005 and later; if you need to remove duplicates from an earlier version please upgrade you SQL Server version.  Just kidding!  Use the technique in method 2.  Actually you really should upgrade your version of SQL Server…

This method uses the Common Table Expression functionality and row_number function which was introduced in Microsoft SQL Server 2005.   The key strategy to this method is to generate a number next to the  records which denotes the row number.

So in essence we want to transform this:

Duplicated Records

Duplicated Records

To this:


Duplicated Results with Numbers

And remove all instances of those rows which the row number column do not correspond to 1.

Below is a script which can be used to demonstrate this.   Also you can use this link in SQL Fiddle.

Method 2:

This method should work on all versions for SQL Server.  It basically works by copying the unique rows out of the original table into a temporary table, and then deleting all records from the original table;  the unique rows from the temporary table are then copied into the original table.

Link to SQL Fiddle is here.

— Create Table

How to start and stop Microsoft SQL Server from the command line

Start a command prompt using the “Run as administrator” option.  If you do not use this option you may be a message that ‘Access is denied.’

To start the default instance of SQL Server type the following in command line:

To stop the default instance of SQL Server type the following in the command line:

Equivalent of DUAL table in Microsoft SQL Server

I spent quite a few few years developing PL/SQL on Oracle databases and had grown accustomed to its syntax.  One of the things which I didn’t enjoy was that the ‘FROM’ clause in a SELECT statement was required (it is not in TSQL).  This causes a  bit of a problem when all you want to do is do retrieve 1 row with a result.  The workaround for this problem was to introduce a dummy table which just contains one row which you could use in your queries.  This table was called DUAL and has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.

To demonstrate the use of DUAL, the following displays the use of a SELECT statment that calculates 1+1 in TSQL and PL/SQL:



The name DUAL seems a bit odd for a table which just has one record in it.  The reason for this is that it originally had two records and was intended to be used to return two records when being used.   There is a bit of history which explains this on wikipedia article.

What if you wanted to port existing code from PL/SQL to TSQL and your code already utilizes DUAL?  I would personally rewrite it as you generally have to write rewrite code anyways but the other option is to actually recreate the DUAL table in your database.   The following code will assist creating this:

If your a TSQL programmer working on a PLSQL you will need to remember to utilize the DUAL table,

C# Methods with Unlimited Parameters

To create a method that can take unlimited parameters, utilize the params keyword in the method declaration.  This allows you to specify a method parameter that take an argument where the number of arguments is variable.   You are not allowed to have any other method parameters declared after a params method parameter.

MSDN resource: params



How to determine the version and edition of SQL Server

Connect to the instance of SQL Server, and then run the following query (this will work for all versions of SQL Server):

The result form the query will look like the following.

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

For version of SQL Server 2005 and newer you also can use the SERVERPROPERTY function to retrieve additional information.  Executing the following will provide you with the SQL Server product version (i.e. 10.0.2531.0, product level (RTM) and edition information (i.e. Standard, Enterprise):

You can lookup the product version using the following table:

Release Product Version
SQL Server 2012 Service Pack 1 11.00.3000.00
SQL Server 2012 RTM 11.00.2100.60
SQL Server 2008 R2 Service Pack 1 10.50.2500.0
SQL Server 2008 R2 RTM 10.50.1600.1
SQL Server 2008 Service Pack 3 10.00.5500.00
SQL Server 2008 Service Pack 2 10.00.4000.00
SQL Server 2008 Service Pack 1 10.00.2531.00
SQL Server 2008 RTM 10.00.1600.22
SQL Server 2005 Service Pack 4 9.00.5000.00
SQL Server 2005 Service Pack 3 9.00.4035
SQL Server 2005 Service Pack 2 9.00.3042
SQL Server 2005 Service Pack 1 9.00.2047
SQL Server 2005 RTM 9.00.1399
SQL Server 2000 Service Pack 4 8.00.2039
SQL Server 2000 Service Pack 3 8.00.760
SQL Server 2000 Service Pack 2 8.00.534
SQL Server 2000 Service Pack 1 8.00.384
SQL Server 2000 RTM 8.00.194


Where to find information about the latest SQL Server builds use the following link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;957826

About Carlos Ferreira

Put Bio here.

%d bloggers like this: