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:
SELECT LTRIM(RTRIM(' hello world '))
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.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: Carlos Ferreira
-- Create date: January 29 2014
-- Description: TRIM function (similar to Oracle)
CREATE FUNCTION Trim
-- Declare the return variable here
DECLARE @stringTrimed Varchar(Max)
SET @stringTrimed = ltrim(rtrim(@string))
-- Return the result of the function
After running the above script you could test it by running the following:
SELECT dbo.Trim(' test me ')