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)



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,

%d bloggers like this: