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:





Insert data into new (or existing table) from another (T-SQL, PL/SQL, SQL Server, Oracle)

Often times we wish to copy data from one table into another.  The methods of doing this where the new table does not exist will be slightly different from the case in which it does exist.  Also I will show display the method of doing this in T-SQL and PL/SQL (just because I get confused all the time when I switch databases Smile ).

SQL copy tables to new table

When performing this command, a table will created using the same data types as the columns which as selected in the select statement.  This is usually very useful when making a copy of the existing table before making any alterations to the data.




SQL copy tables to existing table

These following when executed will insert data into already existing table.  It will match fields based on name if you choose to list the fields in the select clause.  This normally useful for when you need to reinsert data (don’t ask why… Smile)



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 create a blocking transaction in SQL Server – T-SQL (exclusive lock)

Excuse the odd topic.   I often like to experiment with SQL Server and create odd situations which we normally should be avoiding.  One of those things is creating SQL Statement which get blocked and creating a timeout.  One of the simplest ways to run a query against a table which is locked in another in another session.  To do this do the following:

1) Create a lock on a table.  

The example below demonstrates a lock on the Person table in the AdventureWorks2012 sample table.    The key points which make this work is to create a transaction, and then issuing a query using the TABLOCKX and HOLDLOCK table lock hints.  This will force an exclusive lock on the table.  To keep the lock for significant amount of time we specify that the transaction stays alive for a minute by introducing the WAITFOR DELAY ’00:01:00′ command, which will prevent any further execution of statements until a minute has passed.

2)  Now open up another session in SQL Server (new query from Microsoft SQL Server Management Studio), and execute the following query:

You will notice that after this query is excuted, the query seems to run forever.  It will actually never  execute until the query in step 1 is finished.

3) Check for blocked transaction.  You can find this by opening up an new query in Microsoft SQL Server Management Studio and executing the following query:

I will explain in more detail how this blocking query works in a later post.

If you have any other tips on how to create a blocked transaction please share them in the comments below.

T-SQL – Search Stored Procedure Code for Text

Often I have to search for stored procedures in SQL Server to find pieces of text.   The motivation for this is to find procedures which use a table or to look for code which the developer has done silly things like hardcoded directories (Boris….).  Luckly you can query sys.procedures to generally find the information that you need.  Below is an example of query that searches for stored procedures for the word ‘c:\*’.


Technet – sys.procedures

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

How to get the column names of a table in SQL Server (T-SQL)

The following query can be run (substitute the ‘TableName’ in the query with the table name you want column information on) to get the column names for any table in T-SQL.

To get information on all the columns for all tables in a database use the following query:

SQL Server also provides a stored procedure (‘sp_help’) which is able to display information on any object listed in sysobjects.   To use this to get information on a table execute the following:

%d bloggers like this: