blog

Home / DeveloperSection / Blogs / Trim method in SQL Server

Trim method in SQL Server

AVADHESH PATEL4033 14-Feb-2013

Trim method in SQL Server

SQL server does not have Trim method, but for trimming blank spaces (leading and trailing) from string we have used LTRIM and RTRIM method in SQL Server. User can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality. For example

Trim in Simple Query

SELECTRTRIM(LTRIM('      SQL Server Trim() Demo       '))AS Trim_String;

It return ‘SQL Server Trim() Demo ’ string without white space.

Trim using function

CREATEFUNCTION dbo.TRIM(@string VARCHAR(MAX))

RETURNSVARCHAR(MAX)

BEGIN

RETURNLTRIM(RTRIM(@string))

END

SELECT dbo.TRIM('      SQL Server Demo       ')AS Trim_String;

Trim using function with table in 2008

-- Create Table

CREATETABLE TrimDemo

(

ID TINYINTNOTNULLIDENTITY (1, 1),

StringCol VARCHAR(150)NOTNULL,

TrimmedCol ASLTRIM(RTRIM(StringCol))

)ON [PRIMARY]

GO

-- Insert data into table

INSERTINTO TrimDemo

([StringCol])

SELECT' SQL Server'

UNION

SELECT'SQL Server 2005    '

UNION

SELECT' SQL Server 2008 '

UNION

SELECT'SQL Server 2012'

GO

-- Select table

SELECT*FROM TrimDemo

GO

Note: If user inserts blank/white spaces between two strings then LTRIM and RTRIM not remove that, it’s removing only starting and ending blank/white spaces.


Updated 18-Sep-2014
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By