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:

References

 PL/SQL TRIM

TSQL LTRIM

TSQL RTRIM

Writing CSV files using CSVHelper package (C#, IEnumerable)

CSV

This is a follow-up on my post on how to read a CSV file using Josh Close’s CsvHelper.  CsvHelper is a fast and flexible .NET library for reading and writing CSV files. Below I will now show how to write CSV files … [Continue reading]

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

wlEmoticon-smile.png

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 … [Continue reading]

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

image.png

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: [crayon-54795fdd81e1f693556950/] Into something that looks like this: The Solution: The … [Continue reading]

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

Problem: I recently received a script which creates a database and loads data.  I was unfortunately too large for SQL Server Management Studio to process. Solution: You can use the sqlcmd utility to run the script file. The example below … [Continue reading]

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

Duplicated Records

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 … [Continue reading]

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 … [Continue reading]

About Me

[aboutme username="ferreiracarlos"] … [Continue reading]

Reading CSV files using CSVHelper package (C#, IEnumerable)

I have come across many times the need to incorporate the reading of CSV files in an application.  Below I will show how to use Josh Close’s CsvHelper to  import a CSV file which looks like the following (file will be included … [Continue reading]

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 … [Continue reading]

%d bloggers like this: