SQL Server - String Functions

This article describes string functions in Microsoft SQL Server.

Overview

Function	Description
LEN()	Length of string
SUBSTRING()	Substring at the specified position and length
LTRIM()	Removes leading blank characters
RTRIM()	Removes trailing blank characters

SQL Server - LEN
Syntax	LEN(string)
Description	Returns the number of characters in string
Notes	Excludes trailing blanks

Example:

SELECT LEN('Boston') AS Length
GO 

Length 
------
     6

SQL Server LEN - Equivalents in Other Databases

Database	Equivalent
Oracle	LENGTH(string) - Includes trailing blanks for CHAR

SQL Server - SUBSTRING
Syntax	SUBSTRING(string, start_position, length)
Description	Returns substring of string at the specified position start_position and length characters long.

Example:

SELECT SUBSTRING('NEW YORK', 5, 4) AS Substr
GO
 
Substr
------
YORK

SQL Server SUBSTRING - Equivalents in Other Databases

Database	Equivalent
Oracle	SUBSTR(string, start_position, length) - length is optional

SQL Server - LTRIM
Syntax	LTRIM(string_expr)
Description	Returns a string_expr after removing leading blanks.
Return Type	nvarchar or ntext

Example:

SELECT LTRIM('  Move Leading Blanks') AS LTRIM
GO

LTRIM
-----
Move Leading Blanks

SQL Server LTRIM - Equivalents in Other Databases

Database	Equivalent
Infrormix	TRIM - Removes leading or trailing pad characters from a string.

SQL Server - RTRIM
Syntax	RTRIM(string_expr)
Description	Returns a string_expr after truncating all trailing blanks.
Return Type	nvarchar or ntext

Example:

SELECT RTRIM('  Trailing Blanks  ') AS RTRIM
GO

RTRIM
-----
  Trailing Blanks

SQL Server RTRIM - Equivalents in Other Databases

Database	Equivalent
Infrormix	TRIM - Removes leading or trailing pad characters from a string.