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:
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Carlos Ferreira -- Create date: January 29 2014 -- Description: TRIM function (similar to Oracle) -- ============================================= CREATE FUNCTION Trim ( @string Varchar(Max) ) RETURNS Varchar(Max) AS BEGIN -- Declare the return variable here DECLARE @stringTrimed Varchar(Max) SET @stringTrimed = ltrim(rtrim(@string)) -- Return the result of the function RETURN @stringTrimed END GO |
After running the above script you could test it by running the following:
|
1 |
SELECT dbo.Trim(' test me ') |
References
About Carlos Ferreira
Leave a Reply