TRIM function

Akash Ahlawat

15y2kSQL

What is the use of TRIM function in SQL?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak15y ago
Accepted answer
Hi Akash,

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:

    * MySQL: TRIM(), RTRIM(), LTRIM()
    * Oracle: RTRIM(), LTRIM()
    * SQL Server: RTRIM(), LTRIM()

The syntax for these trim functions are:

TRIM([[LOCATION] [remstr] FROM ] str): [LOCATION] can be either LEADING, TRAILING, or BOTH. This function gets rid of the [remstr] pattern from either the beginning of the string or the end of the string, or both. If no [remstr] is specified, white spaces are removed.

LTRIM(str): Removes all white spaces from the beginning of the string.

RTRIM(str): Removes all white spaces at the end of the string.

Example 1:

SELECT TRIM('   Sample   ');

Result:

'Sample'

Example 2:

SELECT LTRIM('   Sample   ');

Result:

'Sample   '

Example 3:

SELECT RTRIM('   Sample   ');

Refer

http://www.1keydata.com/sql/sql-trim.html

Thanks
Akash Ahlawat15y ago
Thanks guys
Aarti15y ago
Hi Akash,

SQL Server does not have TRIM() function. it can do easily by using LTRIM() and RTRIM() together and simulate TRIM() functionality.

SELECT RTRIM(LTRIM(' Word ')) AS Answer;
output : Word

Here I created A User Defined Function For Trim function
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

to test above UDF running following statement where there are leading and trailing spaces around word.

SELECT dbo.TRIM(' Hello World ');

It will return string in result window as
'Hello World'

Thanks.

If it helps you, please mark it as answer