SQL Server Scalar String Functions

Scalar string functions return a single value (scalar) from the input string arguments. These functions are essential for manipulating and processing text data within your SQL Server databases.

Overview

SQL Server provides a rich set of built-in scalar string functions to handle various text operations such as concatenation, substring extraction, case conversion, pattern matching, and more. Understanding these functions can significantly improve your ability to query and manage string data effectively.

Common Scalar String Functions

• LEN (Length)

  Returns the number of characters of the expression, after removing trailing blanks.

  LEN ( character_expression )
  Example:

  SELECT LEN('Hello World ');

• LEFT

  Returns the left part of a character string with the specified number of characters.

  LEFT ( character_expression , integer_expression )
  Example:

  SELECT LEFT('SQL Server', 4); -- Returns 'SQL '

• RIGHT

  Returns the right part of a character string with the specified number of characters.

  RIGHT ( character_expression , integer_expression )
  Example:

  SELECT RIGHT('SQL Server', 5); -- Returns 'Server'

• SUBSTRING (or SUBSTR)

  Returns a part of a character string starting at the specified start position and with the specified length.

  SUBSTRING ( expression , start , length )
  Example:

  SELECT SUBSTRING('SQL Server Functions', 5, 6); -- Returns 'Server'

• CHARINDEX

  Returns the starting position of a specified expression within a character string.

  CHARINDEX ( substring , expression [ , start_location ] )
  Example:

  SELECT CHARINDEX('Serve', 'SQL Server'); -- Returns 5

• REPLACE

  Returns a character string after replacing all occurrences of a specified substring with another specified substring.

  REPLACE ( string_expression , string_pattern , string_replacement )
  Example:

  SELECT REPLACE('Microsoft SQL Server', 'SQL', 'T-SQL'); -- Returns 'Microsoft T-SQL Server'

• UPPER

  Converts a character string to uppercase.

  UPPER ( character_expression )
  Example:

  SELECT UPPER('sql server'); -- Returns 'SQL SERVER'

• LOWER

  Converts a character string to lowercase.

  LOWER ( character_expression )
  Example:

  SELECT LOWER('SQL SERVER'); -- Returns 'sql server'

• LTRIM

  Returns a character expression after it removes all leading spaces.

  LTRIM ( character_expression )
  Example:

  SELECT LTRIM('   SQL Server'); -- Returns 'SQL Server'

• RTRIM

  Returns a character expression after it removes trailing spaces. It also removes trailing nonblank characters specified by the user.

  RTRIM ( character_expression )
  Example:

  SELECT RTRIM('SQL Server   '); -- Returns 'SQL Server'

• CONCAT

  Concatenates two or more string expressions either as strings or as the result of other expressions.

  CONCAT ( string_expression1, string_expression2 [, string_expression ] ... )
  Example:

  SELECT CONCAT('SQL', ' ', 'Server'); -- Returns 'SQL Server'

  Note: The + operator can also be used for string concatenation, but CONCAT handles NULL values more gracefully by treating them as empty strings.

• REVERSE

  Returns the reversed expression of the input character string.

  REVERSE ( character_expression )
  Example:

  SELECT REVERSE('SQL'); -- Returns 'LQS'

Function Behavior with NULL

See Also

• Aggregate Functions
• Scalar Numeric Functions
• Data Types