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:
Post a Comment