LTRIM String Function
Prior to SQL Server 2022, the LTRIM string function removes just the space character char(32) from the start of a string. However, starting from SQL Server 2022, the LTRIM string function was updated to include the removal of other special characters passed in the second argument instead of just the space character char(32) from the start of a string.
Syntax
Syntax for SQL Server prior to SQL Server 2022 (16.x):
LTRIM ( character_expression )
Syntax for SQL Server 2022 (16.x) and later, Azure SQL Managed Instance, Azure SQL Database, Azure Synapse Analytics, and Microsoft Fabric:
Important: You need your database compatibility level set to 160 to use the optional characters argument.
LTRIM ( character_expression , [ characters ] )
Arguments
character_expression
An expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type, except text, ntext, and image, that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.
characters
A literal, variable, or function call of any non-LOB character type (nvarchar, varchar, nchar, or char) containing characters that should be removed. nvarchar(max) and varchar(max) types aren't allowed.
Return types
Returns a character expression with a type of string argument where the space character char(32) or other specified characters are removed from the beginning of a character_expression. Returns NULL if input string is NULL.
Sample Uses of the LTRIM String Function
Usage #1: Remove or Trim Leading Zeros in a String (Prior to SQL Server 2022)
Prior to SQL Server 2022, the LTRIM string function only accepts one argument, which is for the string whose space characters (char(32)) at the start of the string are to be removed. The following query uses the LTRIM string function to trim leading zeros and is discussed further in the Trim Leading Zeros Function article.
DECLARE @Input VARCHAR(20)
SET @Input = '00001240-01010'
SELECT REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0') AS [Output]
| Output |
|------------|
| 1240-01010 |
Figure 1: LTRIM Usage #1 - Remove or Trim Leading Zeros in a String (Prior to SQL Server 2022)
Usage #2: Remove or Trim Leading Zeros in a String (SQL Server 2022)
If you are using SQL Server 2022, you can use the LTRIM string function to trim leading zeros from the start of a string. Simply pass a value of '0' as the second argument of the LTRIM function:
SELECT [Input Type], [Input], LTRIM([Input], '0') AS [Output]
FROM (VALUES ('All Numbers with Leading Zeros', '000123456789'),
('All Zeros', '00000000000'),
('Number Separated by Dashes', '0000123-456-789'),
('MM/dd/yyy Date Format', '09/09/2024'),
('All String', 'No Zeros To Trim Here'),
('Leading Spaces',' 0000888-8888'),
('Number with No Leading Zeros', '18005551234'),
('Alphanumeric String', '00An-Alphanumeric-String-00'))
AS [SampleStrings] ( [Input Type], [Input] );
| Input Type | Input | Output |
|--------------------------------|-----------------------------|---------------------------|
| All Numbers with Leading Zeros | 000123456789 | 123456789 |
| All Zeros | 00000000000 | |
| Number Separated by Dashes | 0000123-456-789 | 123-456-789 |
| MM/dd/yyy Date Format | 09/09/2024 | 9/09/2024 |
| All String | No Zeros To Trim Here | No Zeros To Trim Here |
| Leading Spaces | 0000888-8888 | 0000888-8888 |
| Number with No Leading Zeros | 18005551234 | 18005551234 |
| Alphanumeric String | 00An-Alphanumeric-String-00 | An-Alphanumeric-String-00 |
Figure 2: LTRIM Usage #2 - Remove or Trim Leading Zeros in a String (SQL Server 2022)
Usage #3: Remove or Trim Trailing Spaces Without Using RTRIM
The RTRIM string function removes space character char(32) or other specified characters from the end of a string. The removal or trimming of trailing spaces can be done without the use of RTRIM but with the use of the LTRIM string function as shown below:
DECLARE @Input VARCHAR(100)
SET @Input = 'The quick brown fox jumps over the lazy dog. '
SELECT REVERSE(LTRIM(REVERSE(@Input))) AS [Output]
| Output |
|----------------------------------------------|
| The quick brown fox jumps over the lazy dog. |
Figure 3: LTRIM Usage #3 - Remove or Trim Trailing Spaces Without Using RTRIM
What the query does is to first reverse the input string using the REVERSE string function. Now, all the space characters are in front of the string. The reversed string is then left-trimmed with all the space characters using the LTRIM string function then reversed back again to get the desired result.
Usage #4: Determine the Position of the First Non-Space Character
Another sample usage of the LTRIM string function is in determining the position of the first non-space character from a string.
DECLARE @Input VARCHAR(100)
SET @Input = ' SQL Server Helper'
SELECT LEN(@Input) - LEN(LTRIM(@Input)) + 1 AS [Location]
| Location |
|----------|
| 7 |
Figure 4: LTRIM Usage #4 - Determine the Position of the First Non-Space Character
What the query does is to subtract the length of the string after trimming the leading spaces from the length of the original string. This will give you the number of leading spaces that was removed. Adding 1 to that result gives you the location of the first non-space character from the string.
Related Articles: