TRIM function

Question

What is the use of TRIM function in SQL?

Satyapriya Nayak · 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

Aarti · Answer

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

Akash Ahlawat · Answer

Thanks guys

TRIM function

Answers (3)