MSDN Documentation

MDX Functions - String

This section details the string manipulation functions available in Multidimensional Expressions (MDX).

Description

MDX provides a rich set of functions for working with string data. These functions allow you to manipulate, format, and extract information from string values within your Analysis Services cubes.

Key String Functions

STR()

Converts a numeric expression to a string.

Syntax:
STR(numeric_expression)
Example: 
SELECT STR(123.45) ON COLUMNS FROM MyCube
This would return the string "123.45".

FORMAT()

Formats a value according to a specified format string.

Syntax:
FORMAT(value, format_string)
Example: 
SELECT FORMAT(NOW(), 'yyyy-MM-dd HH:mm:ss') ON COLUMNS FROM MyCube
This would return the current date and time formatted as "YYYY-MM-DD HH:MM:SS".

LEFT()

Returns the leftmost characters of a string.

Syntax:
LEFT(string_expression, length)
Example: 
SELECT LEFT('Microsoft SQL Server', 10) ON COLUMNS FROM MyCube
This would return the string "Microsoft ".

RIGHT()

Returns the rightmost characters of a string.

Syntax:
RIGHT(string_expression, length)
Example: 
SELECT RIGHT('Microsoft SQL Server', 6) ON COLUMNS FROM MyCube
This would return the string "Server".

SUBSTRING()

Extracts a substring from a string.

Syntax:
SUBSTRING(string_expression, start_position, length)
Example: 
SELECT SUBSTRING('Microsoft SQL Server', 11, 3) ON COLUMNS FROM MyCube
This would return the string "SQL".

LEN()

Returns the number of characters in a string.

Syntax:
LEN(string_expression)
Example: 
SELECT LEN('Analysis Services') ON COLUMNS FROM MyCube
This would return the number 17.

UPPER()

Converts a string to uppercase.

Syntax:
UPPER(string_expression)

LOWER()

Converts a string to lowercase.

Syntax:
LOWER(string_expression)

REPLACE()

Replaces all occurrences of a substring with another substring.

Syntax:
REPLACE(string_expression, string_to_replace, replacement_string)

TRIM()

Removes leading and trailing spaces from a string.

Syntax:
TRIM(string_expression)

You can also specify characters to trim from the left or right using LTRIM() and RTRIM() respectively.

CONCAT()

Concatenates two or more strings.

Syntax:
CONCAT(string_expression1 [, string_expression2...])

Alternatively, the + operator can be used for string concatenation.

Further Reading