Wednesday, 27 September 2017

TRIM Function to Remove Blank Spaces From a String in SQL Server

Often while entering data in the tables or during bulk insertions, there are high chances of accidently adding white space either in the trailing or leading end of the string. Such instances can cause very annoying for the users to see the results in a shabby format. To overcome the problem of blank space in the string, we can create the TRIM() function and pass the string as parameter to the function.

Before moving to create the trim function, let’s see the two types of Trim functions that are available in SQL Server

1.  LTRIM - This function helps in removing the blank spaces from the leading end of the string
2.  RTRIM - This function helps in removing the blank spaces from the trailing end of the string

Using the above function in SQL Server to trim the string of an existing table in a database. The initial step is to create the table and insert some values to it with blank spaces. For example, In the CusName column insert names with the blank spaces both front and back of the string.

USE SQLArena_Test
GO
Create table TestDB
(
EID int null,
CusID nvarchar(100) null,
CusName Varchar(100) null
)

From the above table created, in case the names in “CusName” consists of leading and trailing spaces, then use the below command:

UPDATE TestDB SET CusName= LTRIM(RTRIM(CusName))

In the update statement, both LTRIM() and RTRIM() functions are applied while passing the CusName as the parameter. Both the functions are needed to apply in SSMS or Oracle to remove unwanted white spaces. In case if we want to create a function named “Trim” then we can create the function as show:

use SQLArena_Test

GO

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END

GO
SELECT dbo.TRIM('SQLArena')
GO

Additionally, using these function in JOINT and WHERE clause can reduce the performance drastically and using these in production should be into consideration as the query run-time can increase very high. 

0 comments: