SQL - LTRIM() Function

---

---

An SQL LTRIM() is an inbuilt function that is used to remove the leading white spaces that are found on the left side, from a given string or, from the string values in a column of a table.

In addition to the white spaces you can also remove the characters or, substrings (that exists on the left side) from a string using this function. To do so, we need to pass the desired string that is to be removed as a parameter (optional).

After removing the leading white-spaces or specified substring from a string, this function returns the resultant values in the form of a string.

In other programming languages string has written inside the double quote “string value”, but here in SQL, the string must be written in single quote(‘’) as shown below −

‘String value’;

Syntax

Following is the syntax of the SQL LTRIM() function in SQL −

LTRIM(string, [trim_string]);

Parameters

• string − It is a string from which the leading white-spaces or characters would be removed.

• trim_string − It is an optional parameter that specifies the characters to be removed from the string.

Example

In the following example, we are using the SQL LTRIM() function to remove the leading spaces from the input string ‘ Hello World ’.

SELECT LTRIM('       Hello World    ');

Output

Following is the output of the above query −

+---------------------------------+
| LTRIM('       Hello World    ') |
+---------------------------------+
| Hello World                     |
+---------------------------------+

By passing the local variable

In SQL we can declare a variable as like other programming languges. Using the ‘Declare’ keyword and ‘@’ symbol, we can declare a variable.

Syntax

Following is the syntax to declare a variable in SQL −

DECLARE @VAR_NAME AS DATATYPE;

Where, the ‘@’ is a symbol that indefies the local variable in SQL.

Example

When we pass the local variable to the LTRIM() function.

In the following example,we are declaring a variable type varchar() named Name with the value ‘ ROHAN ‘. Then, we pass this variable to the LTRIM() function to remove the leading white space from the given string.

DECLARE
@NAME AS VARCHAR(20) = ' ROHAN ';
PRINT 'Before using the LTRIM() function the string is: ' + @NAME;
PRINT 'After using the LTRIM() function the new string is: ' + LTRIM(@NAME);

Output

The above SQL query produces the following output −

Before using the LTRIM() function the string is:  ROHAN 
After using the LTRIM() function the new string is: ROHAN

Using SET keyword to assign the variable value

In other programming languages, we can directly assign the value to the declared variable. In SQL, however, we must use the SET keyword when assigning a value to a declared variable.

Syntax

Following is the syntax to assign value to declared variable in SQL −

SET @VAR_NAME = VALUE;

Example

Here, we have another example of the LTRIM() function, in this program, first, we will declare a variable named CITY, then we assign the value ‘ HYDERABAD ‘; to it using the SET keyword, and then we will pass this variable as an argument to the LTRIM() function to remove the leading white spaces from the string.

DECLARE 
--DECLARE A VARIBALE
@CITY AS VARCHAR(30);
--ASSIGN VALUE TO IT...
SET @CITY = '  HYDERABAD ';
--PRINTING VALUE
PRINT 'Before using the LTRIM() function: ' + @city;
--USING THE LTRIMG() FUNCTION
PRINT 'After using the LTRIMG() function: ' + LTRIM(@CITY);

Output

On executing the above query, it will produce the following output −

Before using the LTRIM() function:   HYDERABAD 
After using the LTRIMG() function: HYDERABAD

Example

When we pass an optional parameter to the LTRIM() method, this method removes the specified character found on the left side from a string.

In the following example,we are declaring a variable named VALUE and TRIM_STRING, assigning the value using the SET keyword. Then, we are using the LTRIM() function to remove the whitespace and characters from the string ‘####02324#’, by the specific optional parameter value '#'.

DECLARE
@VALUE AS VARCHAR(10);
DECLARE
@TRIM_STRING AS VARCHAR(5);
SET @VALUE = '####02324#';
SET @TRIM_STRING = '#';
PRINT 'String is: ' + @VALUE;
PRINT 'Trim string(opional parameter:): ' + @TRIM_STRING;
PRINT 'After using the LTRIM() function: ' + LTRIM(@value, @TRIM_STRING);

Output

On executing the above query, it will produce the following output −

String is: ####02324#
Trim string(opional parameter:): #
After using the LTRIM() function: 02324#

Example

We can also use the LTRIM() function with the table columns to remove leading white-spaces and specified characters from the column data. Let’s create a table by executing the following SQL statement

create table CUSTOMERS(
   ID INT NOT NULL, 
   NAME VARCHAR(15) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(10, 4), PRIMARY KEY(ID)
);

Then, insert some records into the Customers table by using the INSERT statement as shown below −

insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

Following query removes the specified characters from the values of the column "NAME. In here, since the specified characters R, K, C are the leading characters of some of the names, this query removes the ‘R’ from the ‘Remesh’, ‘K’ from 'Khilan', Kaushik, Komal and ‘C’ from the 'Chaitali'.

SELECT LTRIM(NAME, 'RKC') AS LTRIM_Function FROM CUSTOMERS; 

Output

After executing the above statement, it produces the following output −

+---------------+
|LTRIM_Function |
+---------------+
|    amesh      |
|    hilan      |
|    aushik     |
|    haitali    |
|    Hardik     |
|    omal       |
|    Muffy      |
+---------------+